﻿Imports SS
Imports SS.Databases
Imports SS.Databases.Connecter
Imports SS.ApplicationBlock
Imports System
Imports System.Data
Imports System.Data.OracleClient

Public Class Report

	Public Shared Function YearList() As DataTable
		Using connOraSelect As New DBOracle(Common.Utility.ConnectString, False)
			Return connOraSelect.GetDataTable("select * from " & Common.Utility.Schema & ".RNWM_YEAR_REPORT order by AD_YEAR", "YearList")
		End Using
	End Function

	Private Shared Function Excute(ByVal sql As String) As DataTable
		Using connOraSelect As New DBOracle(Common.Utility.ConnectString, False)
			Return connOraSelect.GetDataTable(sql, "Data")
		End Using
    End Function

#Region " Report 514 Detail "

	Public Shared Function DataUT514_SBM_FTP_SUCCESS(ByVal fromDate As String, ByVal toDate As String) As Data.DataTable
		Dim sb As New Text.StringBuilder
		sb.AppendLine("SELECT RNWT_LOG_SBMFTP_FILE.SBM_MSISDN AS ""MSISDN"", ")
		sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_APP_ID  AS ""Officer Code"", ")
		sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_FIRSTNAME_ENG || ' ' || RNWM_MAIN_OFFICER_INFO.OFFICER_LASTNAME_ENG AS ""Officer Full Name"",")
		sb.AppendLine("RNWT_LOG_SBMFTP_FILE.SBM_PROFILE AS ""Profile"",")
		sb.AppendLine("RNWT_LOG_SBMFTP_FILE.SBM_PACKAGE_ID AS ""Data Package Code"",")
		sb.AppendLine("RNWT_LOG_SBMFTP_FILE.SBM_CREATE_DT AS ""Create Date"",")
		sb.AppendLine("RNWT_LOG_SBMFTP_FILE.SBM_FUNCTION AS ""Function"",")
		sb.AppendLine("RNWT_LOG_SBMFTP_FILE.SBM_STATUS AS ""Status"",")
		sb.AppendLine("RNWT_LOG_SBMFTP_FILE.SBM_CHANNEL AS ""Channel""")
		sb.AppendLine("FROM " & Common.Utility.Schema & ".RNWT_LOG_SBMFTP_FILE")
		sb.AppendLine("LEFT OUTER JOIN " & Common.Utility.Schema & ".RNWM_MAIN_OFFICER_INFO")
		sb.AppendLine("ON RNWT_LOG_SBMFTP_FILE.SBM_MSISDN = RNWM_MAIN_OFFICER_INFO.OFFCIER_APP_TEL")
		sb.AppendLine("WHERE TO_CHAR(TO_DATE(RNWT_LOG_SBMFTP_FILE.SBM_CREATE_DT,'yyyymmdd HH24:MI:SS'),'YYYYmmdd') ")
		sb.AppendLine("BETWEEN '" & fromDate & "' AND '" & toDate & "'")
		sb.AppendLine("AND UPPER(RNWT_LOG_SBMFTP_FILE.SBM_FUNCTION) = UPPER('BUY')")
		sb.AppendLine("AND UPPER(RNWT_LOG_SBMFTP_FILE.SBM_STATUS) = UPPER('SUCCESS') AND RNWT_LOG_SBMFTP_FILE.RECORD_STATUS = 'A' AND RNWT_LOG_SBMFTP_FILE.SBM_PROFILE='TMVMMMLM1'")
		Return Excute(sb.ToString)
	End Function

	Public Shared Function DataUT514_SBM_FTP_ERROR(ByVal fromDate As String, ByVal toDate As String) As Data.DataTable
		Dim sb As New Text.StringBuilder
		sb.AppendLine("SELECT RNWT_LOG_SBMFTP_FILE.SBM_MSISDN AS ""MSISDN"", ")
		sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_APP_ID AS ""Officer Code"", ")
		sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_FIRSTNAME_ENG || ' ' || RNWM_MAIN_OFFICER_INFO.OFFICER_LASTNAME_ENG AS ""Officer Full Name"",")
		sb.AppendLine("RNWT_LOG_SBMFTP_FILE.SBM_PROFILE AS ""Profile"",")
		sb.AppendLine("RNWT_LOG_SBMFTP_FILE.SBM_PACKAGE_ID AS ""Data Package Code"",")
		sb.AppendLine("RNWT_LOG_SBMFTP_FILE.SBM_CREATE_DT AS ""Create Date"",")
		sb.AppendLine("RNWT_LOG_SBMFTP_FILE.SBM_FUNCTION AS ""Function"",")
		sb.AppendLine("RNWT_LOG_SBMFTP_FILE.SBM_STATUS AS ""Status"",")
		sb.AppendLine("RNWT_LOG_SBMFTP_FILE.SBM_CHANNEL AS ""Channel""")
		sb.AppendLine("FROM " & Common.Utility.Schema & ".RNWT_LOG_SBMFTP_FILE")
		sb.AppendLine("LEFT OUTER JOIN " & Common.Utility.Schema & ".RNWM_MAIN_OFFICER_INFO")
		sb.AppendLine("ON RNWT_LOG_SBMFTP_FILE.SBM_MSISDN = RNWM_MAIN_OFFICER_INFO.OFFCIER_APP_TEL")
		sb.AppendLine("WHERE TO_CHAR(TO_DATE(RNWT_LOG_SBMFTP_FILE.SBM_CREATE_DT,'yyyymmdd HH24:MI:SS'),'YYYYmmdd') ")
		sb.AppendLine("BETWEEN '" & fromDate & "' AND '" & toDate & "'")
		sb.AppendLine("AND (UPPER(RNWT_LOG_SBMFTP_FILE.SBM_FUNCTION) <> UPPER('BUY')")
		sb.AppendLine("OR UPPER(RNWT_LOG_SBMFTP_FILE.SBM_STATUS) <> UPPER('SUCCESS')) AND RNWT_LOG_SBMFTP_FILE.RECORD_STATUS = 'A'")
		Return Excute(sb.ToString)
	End Function

	Public Shared Function DataUT514_IVR_FTP_SUCCESS(ByVal fromDate As String, ByVal toDate As String) As Data.DataTable
		Dim sb As New Text.StringBuilder
		sb.AppendLine("SELECT RNWT_LOG_IVRFTP_FILE.IVR_MSISDN AS ""MSISDN"", ")
		sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_APP_ID AS ""Officer Code"", ")
		sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_FIRSTNAME_ENG || ' ' || RNWM_MAIN_OFFICER_INFO.OFFICER_LASTNAME_ENG AS ""Officer Full Name"",")
		sb.AppendLine("RNWT_LOG_IVRFTP_FILE.IVR_PROFILE AS ""Profile"",")
		sb.AppendLine("RNWT_LOG_IVRFTP_FILE.IVR_PACKAGE_ID AS ""Data Package Code"",")
		sb.AppendLine("RNWT_LOG_IVRFTP_FILE.IVR_CREATE_DT AS ""Create Date"",")
		sb.AppendLine("RNWT_LOG_IVRFTP_FILE.IVR_FUNCTION AS ""Function"",")
		sb.AppendLine("RNWT_LOG_IVRFTP_FILE.IVR_STATUS AS ""Status"",")
		sb.AppendLine("RNWT_LOG_IVRFTP_FILE.IVR_CHANNEL AS ""Channel""")
		sb.AppendLine("FROM " & Common.Utility.Schema & ".RNWT_LOG_IVRFTP_FILE")
		sb.AppendLine("LEFT OUTER JOIN " & Common.Utility.Schema & ".RNWM_MAIN_OFFICER_INFO")
		sb.AppendLine("ON RNWT_LOG_IVRFTP_FILE.IVR_MSISDN = RNWM_MAIN_OFFICER_INFO.OFFCIER_APP_TEL")
		sb.AppendLine("WHERE TO_CHAR(TO_DATE(RNWT_LOG_IVRFTP_FILE.IVR_CREATE_DT,'yyyymmdd HH24:MI:SS'),'YYYYmmdd') ")
		sb.AppendLine("BETWEEN '" & fromDate & "' AND '" & toDate & "'")
		sb.AppendLine("AND UPPER(RNWT_LOG_IVRFTP_FILE.IVR_CHANNEL) = UPPER('IVR')")
		sb.AppendLine("AND UPPER(RNWT_LOG_IVRFTP_FILE.IVR_CHANNEL_REFERENCE) = UPPER('Topping complete') AND RNWT_LOG_IVRFTP_FILE.RECORD_STATUS = 'A'")
		Return Excute(sb.ToString)
	End Function

	Public Shared Function DataUT514_IVR_FTP_ERROR(ByVal fromDate As String, ByVal toDate As String) As Data.DataTable
		Dim sb As New Text.StringBuilder
		sb.AppendLine("SELECT RNWT_LOG_IVRFTP_FILE.IVR_MSISDN AS ""MSISDN"", ")
		sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_APP_ID AS ""Officer Code"", ")
		sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_FIRSTNAME_ENG || ' ' || RNWM_MAIN_OFFICER_INFO.OFFICER_LASTNAME_ENG AS ""Officer Full Name"",")
		sb.AppendLine("RNWT_LOG_IVRFTP_FILE.IVR_PROFILE AS ""Profile"",")
		sb.AppendLine("RNWT_LOG_IVRFTP_FILE.IVR_PACKAGE_ID AS ""Data Package Code"",")
		sb.AppendLine("RNWT_LOG_IVRFTP_FILE.IVR_CREATE_DT AS ""Create Date"",")
		sb.AppendLine("RNWT_LOG_IVRFTP_FILE.IVR_FUNCTION AS ""Function"",")
		sb.AppendLine("RNWT_LOG_IVRFTP_FILE.IVR_STATUS AS ""Status"",")
		sb.AppendLine("RNWT_LOG_IVRFTP_FILE.IVR_CHANNEL AS ""Channel""")
		sb.AppendLine("FROM " & Common.Utility.Schema & ".RNWT_LOG_IVRFTP_FILE")
		sb.AppendLine("LEFT OUTER JOIN " & Common.Utility.Schema & ".RNWM_MAIN_OFFICER_INFO")
		sb.AppendLine("ON RNWT_LOG_IVRFTP_FILE.IVR_MSISDN = RNWM_MAIN_OFFICER_INFO.OFFCIER_APP_TEL")
		sb.AppendLine("WHERE TO_CHAR(TO_DATE(RNWT_LOG_IVRFTP_FILE.IVR_CREATE_DT,'yyyymmdd HH24:MI:SS'),'YYYYmmdd') ")
		sb.AppendLine("BETWEEN '" & fromDate & "' AND '" & toDate & "'")
		sb.AppendLine("AND (UPPER(RNWT_LOG_IVRFTP_FILE.IVR_CHANNEL) <> UPPER('IVR')")
		sb.AppendLine("OR UPPER(RNWT_LOG_IVRFTP_FILE.IVR_CHANNEL_REFERENCE) <> UPPER('Topping complete')) AND RNWT_LOG_IVRFTP_FILE.RECORD_STATUS = 'A'")
		Return Excute(sb.ToString)
	End Function

	Public Shared Function DataUT514_IVR_XML_SUCCESS(ByVal fromDate As String, ByVal toDate As String) As Data.DataTable
		Dim sb As New Text.StringBuilder
		sb.AppendLine("SELECT RNWT_LOG_IVRXML_FILE.MOBILE_NO AS ""Mobile No"", ")
		sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_APP_ID AS ""Officer Code"", ")
		sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_FIRSTNAME_ENG || ' ' || RNWM_MAIN_OFFICER_INFO.OFFICER_LASTNAME_ENG AS ""Officer Full Name"",")
		sb.AppendLine("RNWT_LOG_IVRXML_FILE.TRAN_ID,")
		sb.AppendLine("RNWT_LOG_IVRXML_FILE.PP_CODE,")
		sb.AppendLine("RNWT_LOG_IVRXML_FILE.TOPUP_VALUE,")
		sb.AppendLine("RNWT_LOG_IVRXML_FILE.TOPUP_DATE,")
		sb.AppendLine("RNWT_LOG_IVRXML_FILE.RESULT_CODE,")
		sb.AppendLine("RNWT_LOG_IVRXML_FILE.RESULT_DESC")
		sb.AppendLine("FROM " & Common.Utility.Schema & ".RNWT_LOG_IVRXML_FILE")
		sb.AppendLine("LEFT OUTER JOIN " & Common.Utility.Schema & ".RNWM_MAIN_OFFICER_INFO")
		sb.AppendLine("ON RNWT_LOG_IVRXML_FILE.MOBILE_NO = RNWM_MAIN_OFFICER_INFO.OFFCIER_APP_TEL")
		sb.AppendLine("WHERE TO_CHAR(TO_DATE(RNWT_LOG_IVRXML_FILE.TOPUP_DATE,'yyyymmdd HH24:MI:SS'),'YYYYmmdd') ")
		sb.AppendLine("BETWEEN '" & fromDate & "' AND '" & toDate & "'")
		sb.AppendLine("AND UPPER(RNWT_LOG_IVRXML_FILE.RESULT_CODE) = UPPER('0') AND RNWT_LOG_IVRXML_FILE.RECORD_STATUS = 'A'")
		Return Excute(sb.ToString)
	End Function

	Public Shared Function DataUT514_IVR_XML_ERROR(ByVal fromDate As String, ByVal toDate As String) As Data.DataTable
		Dim sb As New Text.StringBuilder
		sb.AppendLine("SELECT RNWT_LOG_IVRXML_FILE.MOBILE_NO AS ""Mobile No"", ")
		sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_APP_ID AS ""Officer Code"", ")
		sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_FIRSTNAME_ENG || ' ' || RNWM_MAIN_OFFICER_INFO.OFFICER_LASTNAME_ENG AS ""Officer Full Name"",")
		sb.AppendLine("RNWT_LOG_IVRXML_FILE.TRAN_ID,")
		sb.AppendLine("RNWT_LOG_IVRXML_FILE.PP_CODE,")
		sb.AppendLine("RNWT_LOG_IVRXML_FILE.TOPUP_VALUE,")
		sb.AppendLine("RNWT_LOG_IVRXML_FILE.TOPUP_DATE,")
		sb.AppendLine("RNWT_LOG_IVRXML_FILE.RESULT_CODE,")
		sb.AppendLine("RNWT_LOG_IVRXML_FILE.RESULT_DESC")
		sb.AppendLine("FROM " & Common.Utility.Schema & ".RNWT_LOG_IVRXML_FILE")
		sb.AppendLine("LEFT OUTER JOIN " & Common.Utility.Schema & ".RNWM_MAIN_OFFICER_INFO")
		sb.AppendLine("ON RNWT_LOG_IVRXML_FILE.MOBILE_NO = RNWM_MAIN_OFFICER_INFO.OFFCIER_APP_TEL")
		sb.AppendLine("WHERE TO_CHAR(TO_DATE(RNWT_LOG_IVRXML_FILE.TOPUP_DATE,'yyyymmdd HH24:MI:SS'),'YYYYmmdd') ")
		sb.AppendLine("BETWEEN '" & fromDate & "' AND '" & toDate & "'")
		sb.AppendLine("AND UPPER(RNWT_LOG_IVRXML_FILE.RESULT_CODE) <> UPPER('0') AND RNWT_LOG_IVRXML_FILE.RECORD_STATUS = 'A'")
		Return Excute(sb.ToString)
	End Function

#End Region

	Public Shared Function DataUT514(ByVal fromDate As String, ByVal toDate As String,
	   ByVal fileType As String, ByVal tranStatus As String) As Data.DataTable

		Dim sb As New Text.StringBuilder
		sb.AppendLine("SELECT INTERFACE_DATE, FILE_TYPE, TRANSACTION_COUNT, ")
		sb.AppendLine("TRANSACTION_STATUS, TRANSACTION_REASON")
		sb.AppendLine("FROM ")
		sb.AppendLine("(")
		sb.AppendLine("    SELECT TO_CHAR(TO_DATE(RNWT_LOG_SBMFTP_FILE.SBM_CREATE_DT,'yyyymmdd HH24:MI:SS'),'dd/mm/YYYY') AS INTERFACE_DATE,")
		sb.AppendLine("    'SBM FTP FILE' AS FILE_TYPE,")
		sb.AppendLine("    COUNT(RNWT_LOG_SBMFTP_FILE.SBMFTP_ID) AS TRANSACTION_COUNT,")
		sb.AppendLine("    'SUCCESS' AS TRANSACTION_STATUS, ")
		sb.AppendLine("    '' AS TRANSACTION_REASON")
		sb.AppendLine("    FROM " & Common.Utility.Schema & ".RNWT_LOG_SBMFTP_FILE")
		sb.AppendLine("    WHERE UPPER(RNWT_LOG_SBMFTP_FILE.SBM_FUNCTION) = UPPER('BUY')")
		sb.AppendLine("    AND UPPER(RNWT_LOG_SBMFTP_FILE.SBM_STATUS) = UPPER('SUCCESS') AND RECORD_STATUS = 'A' AND SBM_PROFILE='TMVMMMLM1'")
		sb.AppendLine("    GROUP BY TO_CHAR(TO_DATE(RNWT_LOG_SBMFTP_FILE.SBM_CREATE_DT,'yyyymmdd HH24:MI:SS'),'dd/mm/YYYY') ")
		sb.AppendLine("    UNION ")
		sb.AppendLine("    SELECT TO_CHAR(TO_DATE(RNWT_LOG_SBMFTP_FILE.SBM_CREATE_DT,'yyyymmdd HH24:MI:SS'),'dd/mm/YYYY') AS INTERFACE_DATE,")
		sb.AppendLine("    'SBM FTP FILE' AS FILE_TYPE,")
		sb.AppendLine("    COUNT(RNWT_LOG_SBMFTP_FILE.SBMFTP_ID) AS TRANSACTION_COUNT,")
		sb.AppendLine("    'ERROR' AS TRANSACTION_STATUS, ")
		sb.AppendLine("    '' AS TRANSACTION_REASON")
		sb.AppendLine("    FROM " & Common.Utility.Schema & ".RNWT_LOG_SBMFTP_FILE")
		sb.AppendLine("    WHERE (UPPER(RNWT_LOG_SBMFTP_FILE.SBM_FUNCTION) <> UPPER('BUY')")
		sb.AppendLine("    OR UPPER(RNWT_LOG_SBMFTP_FILE.SBM_STATUS) <> UPPER('SUCCESS')) AND RECORD_STATUS = 'A'")
		sb.AppendLine("    GROUP BY TO_CHAR(TO_DATE(RNWT_LOG_SBMFTP_FILE.SBM_CREATE_DT,'yyyymmdd HH24:MI:SS'),'dd/mm/YYYY') ")
		sb.AppendLine("    UNION")
		sb.AppendLine("    SELECT TO_CHAR(TO_DATE(RNWT_LOG_IVRFTP_FILE.IVR_CREATE_DT,'yyyymmdd HH24:MI:SS'),'dd/mm/YYYY') AS INTERFACE_DATE,")
		sb.AppendLine("    'IVR FTP FILE' AS FILE_TYPE,")
		sb.AppendLine("    COUNT(RNWT_LOG_IVRFTP_FILE.IVRFTP_ID) AS TRANSACTION_COUNT,")
		sb.AppendLine("    'SUCCESS' AS TRANSACTION_STATUS, ")
		sb.AppendLine("    '' AS TRANSACTION_REASON")
		sb.AppendLine("    FROM " & Common.Utility.Schema & ".RNWT_LOG_IVRFTP_FILE")
		sb.AppendLine("    WHERE UPPER(RNWT_LOG_IVRFTP_FILE.IVR_CHANNEL) = UPPER('IVR')")
		sb.AppendLine("    AND UPPER(RNWT_LOG_IVRFTP_FILE.IVR_CHANNEL_REFERENCE) = UPPER('Topping complete') AND RECORD_STATUS = 'A'")
		sb.AppendLine("    GROUP BY TO_CHAR(TO_DATE(RNWT_LOG_IVRFTP_FILE.IVR_CREATE_DT,'yyyymmdd HH24:MI:SS'),'dd/mm/YYYY') ")
		sb.AppendLine("    UNION")
		sb.AppendLine("    SELECT TO_CHAR(TO_DATE(RNWT_LOG_IVRFTP_FILE.IVR_CREATE_DT,'yyyymmdd HH24:MI:SS'),'dd/mm/YYYY') AS INTERFACE_DATE,")
		sb.AppendLine("    'IVR FTP FILE' AS FILE_TYPE,")
		sb.AppendLine("    COUNT(RNWT_LOG_IVRFTP_FILE.IVRFTP_ID) AS TRANSACTION_COUNT,")
		sb.AppendLine("    'ERROR' AS TRANSACTION_STATUS, ")
		sb.AppendLine("    RNWT_LOG_IVRFTP_FILE.IVR_CHANNEL_REFERENCE AS TRANSACTION_REASON")
		sb.AppendLine("    FROM " & Common.Utility.Schema & ".RNWT_LOG_IVRFTP_FILE")
		sb.AppendLine("    WHERE (UPPER(RNWT_LOG_IVRFTP_FILE.IVR_CHANNEL) <> UPPER('IVR')")
		sb.AppendLine("    OR UPPER(RNWT_LOG_IVRFTP_FILE.IVR_CHANNEL_REFERENCE) <> UPPER('Topping complete')) AND RECORD_STATUS = 'A'")
		sb.AppendLine("    GROUP BY RNWT_LOG_IVRFTP_FILE.IVR_CHANNEL_REFERENCE,")
		sb.AppendLine("    TO_CHAR(TO_DATE(RNWT_LOG_IVRFTP_FILE.IVR_CREATE_DT,'yyyymmdd HH24:MI:SS'),'dd/mm/YYYY') ")
		sb.AppendLine("    UNION")
		sb.AppendLine("    SELECT TO_CHAR(TO_DATE(RNWT_LOG_IVRXML_FILE.TOPUP_DATE,'yyyymmdd HH24:MI:SS'),'dd/mm/YYYY') AS INTERFACE_DATE,")
		sb.AppendLine("    'IVR XML FILE' AS FILE_TYPE,")
		sb.AppendLine("    COUNT(RNWT_LOG_IVRXML_FILE.IVRXML_ID) AS TRANSACTION_COUNT,")
		sb.AppendLine("    'SUCCESS' AS TRANSACTION_STATUS, ")
		sb.AppendLine("    '' AS TRANSACTION_REASON")
		sb.AppendLine("    FROM " & Common.Utility.Schema & ".RNWT_LOG_IVRXML_FILE")
		sb.AppendLine("    WHERE UPPER(RNWT_LOG_IVRXML_FILE.RESULT_CODE) = UPPER('0') AND RECORD_STATUS = 'A'")
		sb.AppendLine("    GROUP BY TO_CHAR(TO_DATE(RNWT_LOG_IVRXML_FILE.TOPUP_DATE,'yyyymmdd HH24:MI:SS'),'dd/mm/YYYY') ")
		sb.AppendLine("    UNION")
		sb.AppendLine("    SELECT TO_CHAR(TO_DATE(RNWT_LOG_IVRXML_FILE.TOPUP_DATE,'yyyymmdd HH24:MI:SS'),'dd/mm/YYYY') AS INTERFACE_DATE,")
		sb.AppendLine("    'IVR XML FILE' AS FILE_TYPE,")
		sb.AppendLine("    COUNT(RNWT_LOG_IVRXML_FILE.IVRXML_ID) AS TRANSACTION_COUNT,")
		sb.AppendLine("    'ERROR' AS TRANSACTION_STATUS, ")
		sb.AppendLine("    RESULT_DESC AS TRANSACTION_REASON")
		sb.AppendLine("    FROM " & Common.Utility.Schema & ".RNWT_LOG_IVRXML_FILE")
		sb.AppendLine("    WHERE UPPER(RNWT_LOG_IVRXML_FILE.RESULT_CODE) <> UPPER('0') AND RECORD_STATUS = 'A'")
		sb.AppendLine("    GROUP BY RESULT_DESC, ")
		sb.AppendLine("    TO_CHAR(TO_DATE(RNWT_LOG_IVRXML_FILE.TOPUP_DATE,'yyyymmdd HH24:MI:SS'),'dd/mm/YYYY') ")
		sb.AppendLine(") WHERE 1=1 ")

		If Not String.IsNullOrEmpty(fileType) Then
			sb.AppendLine(" AND FILE_TYPE = '" & fileType & "'")
		End If

		If Not String.IsNullOrEmpty(tranStatus) Then
			sb.AppendLine(" AND TRANSACTION_STATUS = '" & tranStatus & "'")
		End If

		If (Not String.IsNullOrEmpty(fromDate)) And (String.IsNullOrEmpty(toDate)) Then
			sb.AppendLine(" AND TO_DATE(INTERFACE_DATE,'DD/MM/YYYY') ")
			sb.AppendLine(">= TO_DATE('" & fromDate & "','DD/MM/YYYY') ")
		ElseIf (String.IsNullOrEmpty(fromDate)) And (Not String.IsNullOrEmpty(toDate)) Then
			sb.AppendLine(" AND TO_DATE(INTERFACE_DATE,'DD/MM/YYYY') ")
			sb.AppendLine("<= TO_DATE('" & toDate & "','DD/MM/YYYY') ")
		ElseIf (Not String.IsNullOrEmpty(fromDate)) And (Not String.IsNullOrEmpty(toDate)) Then
			sb.AppendLine(" AND TO_DATE(INTERFACE_DATE,'DD/MM/YYYY') ")
			sb.AppendLine("BETWEEN TO_DATE('" & fromDate & "','DD/MM/YYYY') AND TO_DATE('" & toDate & "','DD/MM/YYYY') ")
		End If

		sb.AppendLine("ORDER BY FILE_TYPE, TO_DATE(INTERFACE_DATE,'DD/MM/YYYY') ")

		Return Excute(sb.ToString)

	End Function

    Public Shared Function DataUT513_Excel(ByVal fromDate As String, ByVal toDate As String, ByVal appTel As String, ByVal appId As String) As Data.DataTable
        Dim sb As New Text.StringBuilder

        sb.AppendLine("SELECT ")

        sb.AppendLine("DEALER_CODE AS ""Dealer Code"",")
        sb.AppendLine("ACTIVITY_DATE AS ""Activity Date"",")
        sb.AppendLine("SBM_PACKAGE_ID AS ""Data Package Code"",")
        sb.AppendLine("case")
        sb.AppendLine("    when PACKAGE_DESC = '' then SBM_DESC")
        sb.AppendLine("    else PACKAGE_DESC")
        sb.AppendLine("end AS ""Data Package Description"",")
        sb.AppendLine("SBM_MSISDN AS ""MSISDN"", ")
        sb.AppendLine("SBM_STATUS AS ""Status"",")
        sb.AppendLine("SBM_CHANNEL AS ""Channel"",")
        sb.AppendLine("COUNT_TAX AS ""Count Trx."",")
        sb.AppendLine("PACKAGE_AMOUNT AS ""SUM (Top up Amount)""")

        sb.AppendLine("FROM ( ")
        sb.AppendLine("SELECT ")
        sb.AppendLine("'10012866' AS DEALER_CODE,")
        sb.AppendLine("TO_CHAR(TO_DATE(RNWT_LOG_SBMFTP_FILE.SBM_CREATE_DT,'yyyymmdd HH24:MI:SS'),'dd/mm/YYYY HH24:MI') AS ACTIVITY_DATE,")
        sb.AppendLine("RNWT_LOG_SBMFTP_FILE.SBM_PACKAGE_ID,")
        sb.AppendLine("RNWT_LOG_SBMFTP_FILE.SBM_DESC,")
        sb.AppendLine("RNWT_LOG_SBMFTP_FILE.SBM_MSISDN, ")
        sb.AppendLine("RNWT_LOG_SBMFTP_FILE.SBM_STATUS,")
        sb.AppendLine("RNWT_LOG_SBMFTP_FILE.SBM_CHANNEL,")
        sb.AppendLine("RNWM_CONFIG_RECONCILE.PACKAGE_ID, ")
        sb.AppendLine("RNWM_CONFIG_RECONCILE.PACKAGE_DESC, ")
        sb.AppendLine("RNWM_CONFIG_RECONCILE.PACKAGE_AMOUNT, ")
        sb.AppendLine("'1' AS COUNT_TAX,")
        sb.AppendLine("'1,000' AS SUM_TOP_UP_AMOUNT")
        sb.AppendLine("FROM RNWT_LOG_SBMFTP_FILE ")
        sb.AppendLine("LEFT OUTER JOIN RNWM_CONFIG_RECONCILE ON RNWT_LOG_SBMFTP_FILE.SBM_PACKAGE_ID = RNWM_CONFIG_RECONCILE.PACKAGE_ID")
        sb.AppendLine("LEFT OUTER JOIN RNWT_LOG_IVRFTP_FILE ON RNWT_LOG_SBMFTP_FILE.SBM_MSISDN = RNWT_LOG_IVRFTP_FILE.IVR_MSISDN")
        sb.AppendLine("WHERE ")
        sb.AppendLine("RNWT_LOG_SBMFTP_FILE.RECORD_STATUS = 'A' ")
        sb.AppendLine("AND (UPPER(RNWT_LOG_IVRFTP_FILE.IVR_CHANNEL) = 'IVR') ")
        sb.AppendLine("AND (UPPER(RNWT_LOG_SBMFTP_FILE.SBM_FUNCTION) = 'BUY') ")
        sb.AppendLine("AND (UPPER(RNWT_LOG_SBMFTP_FILE.SBM_STATUS) = 'SUCCESS')")
        sb.AppendLine("AND (UPPER(RNWT_LOG_SBMFTP_FILE.SBM_CHANNEL) = 'IVR')")

        If (Not String.IsNullOrEmpty(fromDate)) And (String.IsNullOrEmpty(toDate)) Then

            Dim arrFrom() As String = fromDate.Split("/")
            Dim strDteFrom As String = arrFrom(2) & arrFrom(1) & arrFrom(0)

            sb.AppendLine("AND TO_CHAR(TO_DATE(RNWT_LOG_SBMFTP_FILE.SBM_CREATE_DT,'yyyymmdd HH24:MI:SS'),'YYYYmmdd') <= '" & strDteFrom & "'")


        ElseIf (String.IsNullOrEmpty(fromDate)) And (Not String.IsNullOrEmpty(toDate)) Then

            Dim arrTo() As String = toDate.Split("/")
            Dim strDteTo As String = arrTo(2) & arrTo(1) & arrTo(0)

            sb.AppendLine("AND TO_CHAR(TO_DATE(RNWT_LOG_SBMFTP_FILE.SBM_CREATE_DT,'yyyymmdd HH24:MI:SS'),'YYYYmmdd') >= '" & strDteTo & "'")

        ElseIf (Not String.IsNullOrEmpty(fromDate)) And (Not String.IsNullOrEmpty(toDate)) Then

            Dim arrFrom() As String = fromDate.Split("/")
            Dim arrTo() As String = toDate.Split("/")
            Dim strDteFrom As String = arrFrom(2) & arrFrom(1) & arrFrom(0)
            Dim strDteTo As String = arrTo(2) & arrTo(1) & arrTo(0)

            sb.AppendLine("AND TO_CHAR(TO_DATE(RNWT_LOG_SBMFTP_FILE.SBM_CREATE_DT,'yyyymmdd HH24:MI:SS'),'YYYYmmdd') between'" & strDteFrom & "' and '" & strDteTo & "'")
        End If

        sb.AppendLine("AND RNWT_LOG_SBMFTP_FILE.SBM_MSISDN  LIKE '%" & appTel & "%'")
        sb.AppendLine(" GROUP BY RNWT_LOG_SBMFTP_FILE.SBM_MSISDN,")
        sb.AppendLine(" TO_CHAR(TO_DATE(RNWT_LOG_SBMFTP_FILE.SBM_CREATE_DT,'yyyymmdd HH24:MI:SS'),'dd/mm/YYYY HH24:MI')")
        sb.AppendLine(" ,RNWT_LOG_SBMFTP_FILE.SBM_PACKAGE_ID")
        sb.AppendLine(" ,RNWT_LOG_SBMFTP_FILE.SBM_DESC")
        sb.AppendLine(" ,RNWT_LOG_SBMFTP_FILE.SBM_STATUS")
        sb.AppendLine(" ,RNWT_LOG_SBMFTP_FILE.SBM_CHANNEL")
        sb.AppendLine(" ,RNWM_CONFIG_RECONCILE.PACKAGE_ID")
        sb.AppendLine(" ,RNWM_CONFIG_RECONCILE.PACKAGE_DESC ")
        sb.AppendLine(" ,RNWM_CONFIG_RECONCILE.PACKAGE_AMOUNT ")

        sb.AppendLine("ORDER BY TO_CHAR(TO_DATE(RNWT_LOG_SBMFTP_FILE.SBM_CREATE_DT,'yyyymmdd HH24:MI:SS'),'dd/mm/YYYY HH24:MI')")
        sb.AppendLine(") ")


        Return Excute(sb.ToString)

    End Function
    'Public Shared Function DataUT513_Excel(ByVal fromDate As String, ByVal toDate As String, ByVal appTel As String, ByVal appId As String) As Data.DataTable
    '	Dim sb As New Text.StringBuilder

    '	'sb.AppendLine("SELECT ")
    '	'sb.AppendLine("'10012866' AS ""Dealer Code"",")
    '	'sb.AppendLine("TO_CHAR(TO_DATE(RNWT_LOG_SBMFTP_FILE.SBM_CREATE_DT,'yyyymmdd HH24:MI:SS'),'dd/mm/YYYY HH24:MI') AS ""Activity Date"",")
    '	'sb.AppendLine("RNWT_LOG_SBMFTP_FILE.SBM_PACKAGE_ID AS ""Data Package Code"",")
    '	'sb.AppendLine("case")
    '	'sb.AppendLine("    when RNWT_LOG_SBMFTP_FILE.SBM_PACKAGE_ID = '939400V1' then 'Success Unlimited'")
    '	'sb.AppendLine("    else RNWT_LOG_SBMFTP_FILE.SBM_DESC")
    '	'sb.AppendLine("end AS ""Data Package Description"",")
    '	'sb.AppendLine("RNWT_LOG_SBMFTP_FILE.SBM_MSISDN AS ""MSISDN"", ")
    '	'sb.AppendLine("RNWT_LOG_SBMFTP_FILE.SBM_STATUS AS ""Status"",")
    '	'sb.AppendLine("RNWT_LOG_SBMFTP_FILE.SBM_CHANNEL AS ""Channel"",")
    '	'sb.AppendLine("'1' AS ""Count Trx."",")
    '	'sb.AppendLine("'1,000' AS ""SUM (Top up Amount)""")
    '	''sb.AppendLine("FROM " & Common.Utility.Schema & ".RNWT_MAIN_OFFICER_PAID")
    '	''sb.AppendLine("INNER JOIN " & Common.Utility.Schema & ".RNWT_LOG_SBMFTP_FILE")
    '	''sb.AppendLine("INNER JOIN " & Common.Utility.Schema & ".RNWT_LOG_SBMFTP_FILE")
    '	''sb.AppendLine("ON RNWT_MAIN_OFFICER_PAID.SBMFTP_ID = RNWT_LOG_SBMFTP_FILE.SBMFTP_ID")
    '	''sb.AppendLine("INNER JOIN " & Common.Utility.Schema & ".RNWM_MAIN_OFFICER_INFO")
    '	''sb.AppendLine("ON RNWT_MAIN_OFFICER_PAID.OFFICER_ID = RNWM_MAIN_OFFICER_INFO.OFFICER_ID ")
    '	''sb.AppendLine("INNER JOIN RNWT_LOG_IVRFTP_FILE ON RNWT_MAIN_OFFICER_PAID.IVRFTP_ID = RNWT_LOG_IVRFTP_FILE.IVRFTP_ID")
    '	'sb.AppendLine("FROM RNWT_LOG_SBMFTP_FILE  LEFT OUTER JOIN RNWT_LOG_IVRFTP_FILE")
    '	'sb.AppendLine("ON RNWT_LOG_SBMFTP_FILE.SBM_MSISDN = RNWT_LOG_IVRFTP_FILE.IVR_MSISDN")
    '	'sb.AppendLine("LEFT OUTER JOIN RNWT_MAIN_OFFICER_PAID PAID ON PAID.IVRFTP_ID = RNWT_LOG_IVRFTP_FILE.IVRFTP_ID AND PAID.SBMFTP_ID = RNWT_LOG_SBMFTP_FILE.SBMFTP_ID")
    '	'sb.AppendLine("LEFT OUTER JOIN RNWM_MAIN_OFFICER_INFO ON PAID.OFFICER_ID = RNWM_MAIN_OFFICER_INFO.OFFICER_ID")
    '	'sb.AppendLine("")
    '	'sb.AppendLine("WHERE ")
    '	'sb.AppendLine("RNWT_LOG_SBMFTP_FILE.RECORD_STATUS = 'A' ")
    '	'sb.AppendLine("AND (UPPER(RNWT_LOG_IVRFTP_FILE.IVR_CHANNEL) = 'IVR') ")
    '	'sb.AppendLine("AND (UPPER(RNWT_LOG_SBMFTP_FILE.SBM_FUNCTION) = 'BUY') ")
    '	'sb.AppendLine("AND (UPPER(RNWT_LOG_SBMFTP_FILE.SBM_STATUS) = 'SUCCESS')")
    '	'sb.AppendLine("AND (UPPER(RNWT_LOG_SBMFTP_FILE.SBM_CHANNEL) = 'IVR')")
    '	'sb.AppendLine("AND")

    '	'If (Not String.IsNullOrEmpty(fromDate)) And (String.IsNullOrEmpty(toDate)) Then
    '	'	sb.AppendLine("PAID.PAID_DATE")
    '	'	sb.AppendLine(">= TO_DATE('" & fromDate & "','DD/MM/YYYY') AND ")
    '	'ElseIf (String.IsNullOrEmpty(fromDate)) And (Not String.IsNullOrEmpty(toDate)) Then
    '	'	sb.AppendLine("PAID.PAID_DATE")
    '	'	sb.AppendLine("<= TO_DATE('" & toDate & "','DD/MM/YYYY') AND ")
    '	'ElseIf (Not String.IsNullOrEmpty(fromDate)) And (Not String.IsNullOrEmpty(toDate)) Then
    '	'	sb.AppendLine("PAID.PAID_DATE")
    '	'	sb.AppendLine("BETWEEN TO_DATE('" & fromDate & "','DD/MM/YYYY') AND TO_DATE('" & toDate & "','DD/MM/YYYY') AND ")
    '	'End If

    '	'sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_APP_ID LIKE '%" & appId & "%'")
    '	'sb.AppendLine("AND RNWM_MAIN_OFFICER_INFO.OFFCIER_APP_TEL LIKE '%" & appTel & "%'")
    '	'sb.AppendLine("ORDER BY RNWT_LOG_SBMFTP_FILE.SBM_CREATE_DT ASC")

    '	sb.AppendLine("SELECT ")
    '	sb.AppendLine("'10012866' AS ""Dealer Code"",")
    '	sb.AppendLine("TO_CHAR(TO_DATE(RNWT_LOG_SBMFTP_FILE.SBM_CREATE_DT,'yyyymmdd HH24:MI:SS'),'dd/mm/YYYY HH24:MI') AS ""Activity Date"",")
    '	sb.AppendLine("RNWT_LOG_SBMFTP_FILE.SBM_PACKAGE_ID AS ""Data Package Code"",")
    '	sb.AppendLine("case")
    '	sb.AppendLine("    when RNWT_LOG_SBMFTP_FILE.SBM_PACKAGE_ID = '939400V1' then 'Success Unlimited'")
    '	sb.AppendLine("    else RNWT_LOG_SBMFTP_FILE.SBM_DESC")
    '	sb.AppendLine("end AS ""Data Package Description"",")
    '	sb.AppendLine("RNWT_LOG_SBMFTP_FILE.SBM_MSISDN AS ""MSISDN"", ")
    '	sb.AppendLine("RNWT_LOG_SBMFTP_FILE.SBM_STATUS AS ""Status"",")
    '       sb.AppendLine("RNWT_LOG_SBMFTP_FILE.SBM_CHANNEL AS ""Channel"",")
    '       sb.AppendLine("RNWM_CONFIG_RECONCILE.PACKAGE_ID AS ""Package ID"", ")
    '       sb.AppendLine("RNWM_CONFIG_RECONCILE.PACKAGE_DESC AS ""Package Description"", ")
    '       sb.AppendLine("'1' AS ""Count Trx."",")
    '	sb.AppendLine("'1,000' AS ""SUM (Top up Amount)""")
    '       sb.AppendLine("FROM RNWT_LOG_SBMFTP_FILE ")
    '       sb.AppendLine("LEFT OUTER JOIN RNWM_CONFIG_RECONCILE ON RNWT_LOG_SBMFTP_FILE.SBM_PACKAGE_ID = RNWM_CONFIG_RECONCILE.PACKAGE_ID")
    '       sb.AppendLine("LEFT OUTER JOIN RNWT_LOG_IVRFTP_FILE ON RNWT_LOG_SBMFTP_FILE.SBM_MSISDN = RNWT_LOG_IVRFTP_FILE.IVR_MSISDN")
    '       sb.AppendLine("WHERE ")
    '	sb.AppendLine("RNWT_LOG_SBMFTP_FILE.RECORD_STATUS = 'A' ")
    '	sb.AppendLine("AND (UPPER(RNWT_LOG_IVRFTP_FILE.IVR_CHANNEL) = 'IVR') ")
    '	sb.AppendLine("AND (UPPER(RNWT_LOG_SBMFTP_FILE.SBM_FUNCTION) = 'BUY') ")
    '	sb.AppendLine("AND (UPPER(RNWT_LOG_SBMFTP_FILE.SBM_STATUS) = 'SUCCESS')")
    '	sb.AppendLine("AND (UPPER(RNWT_LOG_SBMFTP_FILE.SBM_CHANNEL) = 'IVR')")

    '	If (Not String.IsNullOrEmpty(fromDate)) And (String.IsNullOrEmpty(toDate)) Then

    '		'Dim arrFrom() As String = fromDate.Split("/")
    '		'Dim strDteSBM As String = arrFrom(0) & arrFrom(1) & arrFrom(2)
    '		'Dim strDteIVR As String = arrFrom(2) & arrFrom(1) & arrFrom(0)

    '		'sb.AppendLine("AND RNWT_LOG_IVRFTP_FILE.IVR_FILE_NAME LIKE '%'|| TO_CHAR('" & strDteSBM & "') || '%' AND RNWT_LOG_SBMFTP_FILE.SBM_FILE_NAME LIKE  '%'|| TO_CHAR('" & strDteIVR & "') || '%' ")

    '		Dim arrFrom() As String = fromDate.Split("/")
    '		Dim strDteFrom As String = arrFrom(2) & arrFrom(1) & arrFrom(0)

    '		sb.AppendLine("AND TO_CHAR(TO_DATE(RNWT_LOG_SBMFTP_FILE.SBM_CREATE_DT,'yyyymmdd HH24:MI:SS'),'YYYYmmdd') <= '" & strDteFrom & "'")


    '	ElseIf (String.IsNullOrEmpty(fromDate)) And (Not String.IsNullOrEmpty(toDate)) Then

    '		'Dim arrFrom() As String = toDate.Split("/")
    '		'Dim strDteSBM As String = arrFrom(0) & arrFrom(1) & arrFrom(2)
    '		'Dim strDteIVR As String = arrFrom(2) & arrFrom(1) & arrFrom(0)

    '		'sb.AppendLine("AND RNWT_LOG_IVRFTP_FILE.IVR_FILE_NAME LIKE '%'|| TO_CHAR('" & strDteSBM & "') || '%' AND RNWT_LOG_SBMFTP_FILE.SBM_FILE_NAME LIKE  '%'|| TO_CHAR('" & strDteIVR & "') || '%' ")

    '		Dim arrTo() As String = toDate.Split("/")
    '		Dim strDteTo As String = arrTo(2) & arrTo(1) & arrTo(0)

    '		sb.AppendLine("AND TO_CHAR(TO_DATE(RNWT_LOG_SBMFTP_FILE.SBM_CREATE_DT,'yyyymmdd HH24:MI:SS'),'YYYYmmdd') >= '" & strDteTo & "'")

    '	ElseIf (Not String.IsNullOrEmpty(fromDate)) And (Not String.IsNullOrEmpty(toDate)) Then

    '		Dim arrFrom() As String = fromDate.Split("/")
    '		Dim arrTo() As String = toDate.Split("/")
    '		Dim strDteFrom As String = arrFrom(2) & arrFrom(1) & arrFrom(0)
    '		Dim strDteTo As String = arrTo(2) & arrTo(1) & arrTo(0)
    '		'sb.AppendLine("AND ((RNWT_LOG_IVRFTP_FILE.IVR_FILE_NAME LIKE '%'|| TO_CHAR('" & strDteFromSBM & "') || '%' AND RNWT_LOG_SBMFTP_FILE.SBM_FILE_NAME LIKE  '%'|| TO_CHAR('" & strDteFromIVR & "') || '%') OR (RNWT_LOG_IVRFTP_FILE.IVR_FILE_NAME LIKE '%'|| TO_CHAR('" & strDteToSBM & "') || '%' AND RNWT_LOG_SBMFTP_FILE.SBM_FILE_NAME LIKE  '%'|| TO_CHAR('" & strDteToIVR & "') || '%')) ")

    '		sb.AppendLine("AND TO_CHAR(TO_DATE(RNWT_LOG_SBMFTP_FILE.SBM_CREATE_DT,'yyyymmdd HH24:MI:SS'),'YYYYmmdd') between'" & strDteFrom & "' and '" & strDteTo & "'")
    '	End If

    '	'sb.AppendLine("AND RNWT_LOG_IVRFTP_FILE.IVR_FILE_NAME LIKE '%'|| TO_CHAR('20082011') || '%' AND RNWT_LOG_SBMFTP_FILE.SBM_FILE_NAME LIKE  '%'|| TO_CHAR('20110820') || '%' ")
    '	sb.AppendLine("AND RNWT_LOG_SBMFTP_FILE.SBM_MSISDN  LIKE '%" & appTel & "%'")
    '	sb.AppendLine(" GROUP BY RNWT_LOG_SBMFTP_FILE.SBM_MSISDN,TO_CHAR(TO_DATE(RNWT_LOG_SBMFTP_FILE.SBM_CREATE_DT,'yyyymmdd HH24:MI:SS'),'dd/mm/YYYY HH24:MI')")
    '	sb.AppendLine(" ,RNWT_LOG_SBMFTP_FILE.SBM_PACKAGE_ID,RNWT_LOG_SBMFTP_FILE.SBM_DESC,RNWT_LOG_SBMFTP_FILE.SBM_STATUS,RNWT_LOG_SBMFTP_FILE.SBM_CHANNEL")
    '       sb.AppendLine(" ,RNWM_CONFIG_RECONCILE.PACKAGE_ID, RNWM_CONFIG_RECONCILE.PACKAGE_DESC ")

    '       sb.AppendLine("ORDER BY TO_CHAR(TO_DATE(RNWT_LOG_SBMFTP_FILE.SBM_CREATE_DT,'yyyymmdd HH24:MI:SS'),'dd/mm/YYYY HH24:MI')")


    '	Return Excute(sb.ToString)

    'End Function

	Public Shared Function DataUT513(ByVal fromDate As String, ByVal toDate As String, ByVal appTel As String, ByVal appId As String) As Data.DataTable
		Dim sb As New Text.StringBuilder

		sb.AppendLine("SELECT RNWT_LOG_SBMFTP_FILE.SBM_MSISDN, ")
		sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_APP_ID, ")
		sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_FIRSTNAME_ENG || ' ' || RNWM_MAIN_OFFICER_INFO.OFFICER_LASTNAME_ENG AS OFFICER_FULL_NAME,")
		sb.AppendLine("RNWT_LOG_SBMFTP_FILE.SBM_PROFILE,")
		sb.AppendLine("RNWT_LOG_SBMFTP_FILE.SBM_PACKAGE_ID,")
		sb.AppendLine("RNWT_MAIN_OFFICER_PAID.PAID_DATE,")
		sb.AppendLine("RNWT_LOG_SBMFTP_FILE.SBM_CREATE_DT,")
		sb.AppendLine("RNWT_LOG_SBMFTP_FILE.SBM_FUNCTION,")
		sb.AppendLine("RNWT_LOG_SBMFTP_FILE.SBM_STATUS,")
		sb.AppendLine("RNWT_LOG_SBMFTP_FILE.SBM_CHANNEL")
		sb.AppendLine("FROM " & Common.Utility.Schema & ".RNWT_MAIN_OFFICER_PAID")
		sb.AppendLine("INNER JOIN " & Common.Utility.Schema & ".RNWT_LOG_SBMFTP_FILE")
		sb.AppendLine("ON RNWT_MAIN_OFFICER_PAID.SBMFTP_ID = RNWT_LOG_SBMFTP_FILE.SBMFTP_ID")
		sb.AppendLine("INNER JOIN " & Common.Utility.Schema & ".RNWM_MAIN_OFFICER_INFO")
		sb.AppendLine("ON RNWT_MAIN_OFFICER_PAID.OFFICER_ID = RNWM_MAIN_OFFICER_INFO.OFFICER_ID INNER JOIN RNWT_LOG_IVRFTP_FILE ON RNWT_MAIN_OFFICER_PAID.IVRFTP_ID = RNWT_LOG_IVRFTP_FILE.IVRFTP_ID WHERE ")
		sb.AppendLine("1=1 AND ")

		If (Not String.IsNullOrEmpty(fromDate)) And (String.IsNullOrEmpty(toDate)) Then
			sb.AppendLine("TO_DATE(TO_CHAR(RNWT_MAIN_OFFICER_PAID.PAID_DATE,'DD/MM/YYYY'),'DD/MM/YYYY') ")
			sb.AppendLine(">= TO_DATE('" & fromDate & "','DD/MM/YYYY') AND ")
		ElseIf (String.IsNullOrEmpty(fromDate)) And (Not String.IsNullOrEmpty(toDate)) Then
			sb.AppendLine("TO_DATE(TO_CHAR(RNWT_MAIN_OFFICER_PAID.PAID_DATE,'DD/MM/YYYY'),'DD/MM/YYYY') ")
			sb.AppendLine("<= TO_DATE('" & toDate & "','DD/MM/YYYY') AND ")
		ElseIf (Not String.IsNullOrEmpty(fromDate)) And (Not String.IsNullOrEmpty(toDate)) Then
			sb.AppendLine("TO_DATE(TO_CHAR(RNWT_MAIN_OFFICER_PAID.PAID_DATE,'DD/MM/YYYY'),'DD/MM/YYYY') ")
			sb.AppendLine("BETWEEN TO_DATE('" & fromDate & "','DD/MM/YYYY') AND TO_DATE('" & toDate & "','DD/MM/YYYY') AND ")
		End If

		sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_APP_ID LIKE '%" & appId & "%'")
		sb.AppendLine("AND RNWM_MAIN_OFFICER_INFO.OFFCIER_APP_TEL LIKE '%" & appTel & "%'")
		sb.AppendLine("ORDER BY RNWT_LOG_SBMFTP_FILE.SBM_CREATE_DT ASC")

		Return Excute(sb.ToString)
	End Function

    'Modified SQL by: Ton, Modified SourceCode by: ZaQKy, Modified Date: 01/12/2011
    Public Shared Function DataUT512(ByVal month As String, ByVal year As String, ByVal rvFrom As String, ByVal rvTo As String) As Data.DataTable
        Dim sb As New Text.StringBuilder

        sb.AppendLine("SELECT * FROM (")
        sb.AppendLine("SELECT  TO_DATE('01/" & month & "/" & year & "','DD/MM/YYYY') AS CAL_DATE,RNWM_MAIN_OFFICER_INFO.OFFICER_APP_ID,")
        sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_FIRSTNAME_THA || ' ' || RNWM_MAIN_OFFICER_INFO.OFFICER_LASTNAME_THA AS OFFICER_FULL_NAME,")
        sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFCIER_MEMBER_DATE,")
        sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFCIER_EXPIRD_DATE,")
        sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFCIER_EXPIRD_DATE + 90 AS OFFCIER_LAST_EXPIRD_DATE,")
        sb.AppendLine("(SELECT SM.STATUS_DESC FROM RNWM_STATUS_MAPPING SM WHERE SM.STATUS_ID = RNWM_MAIN_OFFICER_INFO.OFFICER_STATUS) AS OFFICER_STATUS,")
        sb.AppendLine("NVL(RNWF_SEARCH_POINT_MONTH(RNWM_MAIN_OFFICER_INFO.OFFICER_ID, " & year & month & "), 0) ")
        sb.AppendLine("as MONTH_POINT, ")
        sb.AppendLine("NVL(RNWF_SEARCH_POINT_SUMMARY(RNWM_MAIN_OFFICER_INFO.OFFICER_ID, " & year & month & "), 0) ")
        sb.AppendLine("as TOTAL_POINT")
        sb.AppendLine("FROM " & Common.Utility.Schema & ".RNWM_MAIN_OFFICER_INFO")
        sb.AppendLine("LEFT OUTER JOIN " & Common.Utility.Schema & ".RNWT_SUM_OFFICER_POINT")
        sb.AppendLine("ON RNWM_MAIN_OFFICER_INFO.OFFICER_ID = RNWT_SUM_OFFICER_POINT.OFFICER_ID")
        sb.AppendLine("WHERE RNWM_MAIN_OFFICER_INFO.OFFICER_TYPE = 'S' AND TO_CHAR(RNWT_SUM_OFFICER_POINT.CAL_DATE,'MM/YYYY') = '" & month & "/" & year & "') TBLREPORT WHERE 1=1 ")

        If (Not String.IsNullOrEmpty(rvFrom)) And (String.IsNullOrEmpty(rvTo)) Then
            sb.AppendLine("AND TOTAL_POINT >= " & rvFrom)
        ElseIf (String.IsNullOrEmpty(rvFrom)) And (Not String.IsNullOrEmpty(rvTo)) Then
            sb.AppendLine("AND TOTAL_POINT <= " & rvTo)
        ElseIf (Not String.IsNullOrEmpty(rvFrom)) And (Not String.IsNullOrEmpty(rvTo)) Then
            sb.AppendLine("AND TOTAL_POINT BETWEEN " & rvFrom & " AND " & rvTo & "")
                End If
        sb.AppendLine("ORDER BY OFFICER_APP_ID")

        Return Excute(sb.ToString)
    End Function
    ''Modified SQL by: Ton, Modified SourceCode by: ZaQKy, Modified Date: 14/11/2011
    'Public Shared Function DataUT512(ByVal month As String, ByVal year As String, ByVal rvFrom As String, ByVal rvTo As String) As Data.DataTable
    '    Dim sb As New Text.StringBuilder

    '    sb.AppendLine("SELECT * FROM (")
    '    sb.AppendLine("SELECT  TO_DATE('01/" & month & "/" & year & "','DD/MM/YYYY') AS CAL_DATE,RNWM_MAIN_OFFICER_INFO.OFFICER_APP_ID,")
    '    sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_FIRSTNAME_THA || ' ' || RNWM_MAIN_OFFICER_INFO.OFFICER_LASTNAME_THA AS OFFICER_FULL_NAME,")
    '    sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFCIER_MEMBER_DATE,")
    '    sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFCIER_EXPIRD_DATE,")
    '    sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFCIER_EXPIRD_DATE + 90 AS OFFCIER_LAST_EXPIRD_DATE,")

    '    sb.AppendLine("(SELECT SM.STATUS_DESC FROM " & Common.Utility.Schema & ".RNWM_STATUS_MAPPING SM WHERE SM.STATUS_ID = RNWM_MAIN_OFFICER_INFO.OFFICER_STATUS) AS OFFICER_STATUS,")
    '    sb.AppendLine("NVL(RNWF_SEARCH_POINT_SUMMARY(RNWM_MAIN_OFFICER_INFO.OFFICER_ID, " & year & month & "), 0) ")
    '    sb.AppendLine("as TOTAL_POINT")
    '    sb.AppendLine("FROM " & Common.Utility.Schema & ".RNWM_MAIN_OFFICER_INFO")
    '    sb.AppendLine("LEFT OUTER JOIN " & Common.Utility.Schema & ".RNWT_SUM_OFFICER_POINT")
    '    sb.AppendLine("ON RNWM_MAIN_OFFICER_INFO.OFFICER_ID = RNWT_SUM_OFFICER_POINT.OFFICER_ID")
    '    sb.AppendLine("WHERE RNWM_MAIN_OFFICER_INFO.OFFICER_TYPE = 'S' AND TO_CHAR(RNWT_SUM_OFFICER_POINT.CAL_DATE,'MM/YYYY') = '" & month & "/" & year & "') TBLREPORT WHERE 1=1 ")

    '    If (Not String.IsNullOrEmpty(rvFrom)) And (String.IsNullOrEmpty(rvTo)) Then
    '        sb.AppendLine("AND TOTAL_POINT >= " & rvFrom)
    '    ElseIf (String.IsNullOrEmpty(rvFrom)) And (Not String.IsNullOrEmpty(rvTo)) Then
    '        sb.AppendLine("AND TOTAL_POINT <= " & rvTo)
    '    ElseIf (Not String.IsNullOrEmpty(rvFrom)) And (Not String.IsNullOrEmpty(rvTo)) Then
    '        sb.AppendLine("AND TOTAL_POINT BETWEEN " & rvFrom & " AND " & rvTo & "")
    '        End If
    '    sb.AppendLine("ORDER BY OFFICER_APP_ID")

    '    Return Excute(sb.ToString)
    'End Function
    'Public Shared Function DataUT512(ByVal month As String, ByVal year As String, ByVal rvFrom As String, ByVal rvTo As String) As Data.DataTable
    '	Dim sb As New Text.StringBuilder

    '	sb.AppendLine("SELECT * FROM (")
    '	sb.AppendLine("SELECT  TO_DATE('01/" & month & "/" & year & "','DD/MM/YYYY') AS CAL_DATE,RNWM_MAIN_OFFICER_INFO.OFFICER_APP_ID,")
    '	sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_FIRSTNAME_THA || ' ' || RNWM_MAIN_OFFICER_INFO.OFFICER_LASTNAME_THA AS OFFICER_FULL_NAME,")
    '	sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFCIER_MEMBER_DATE,")
    '	sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFCIER_EXPIRD_DATE,")
    '	sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFCIER_EXPIRD_DATE + 90 AS OFFCIER_LAST_EXPIRD_DATE,")
    '	sb.AppendLine("(SELECT SM.STATUS_DESC FROM " & Common.Utility.Schema & ".RNWM_STATUS_MAPPING SM WHERE SM.STATUS_ID = RNWM_MAIN_OFFICER_INFO.OFFICER_STATUS) AS OFFICER_STATUS,")
    '	'sb.AppendLine("DECODE(RNWM_MAIN_OFFICER_INFO.OFFICER_STATUS,'NM','สถานภาพปกติ','EX','อยู่ระหว่างการต่ออายุสาย',")
    '	'sb.AppendLine("'WT','สถานะอยู่ระหว่างตรวจสอบเอกสารการสมัครแบบออนไลน์','EP','หมดสถานภาพสมาชิก','CE','สถานภาพโมฆะ') AS OFFICER_STATUS,")
    '	sb.AppendLine("NVL((   ")
    '	sb.AppendLine("    (RNWT_SUM_OFFICER_POINT.LEFT_POINT_SUMMARY + RNWT_SUM_OFFICER_POINT.RIGHT_POINT_SUMMARY) ")
    '	sb.AppendLine("    - ")
    '	sb.AppendLine("    (")
    '	sb.AppendLine("    SELECT NVL(SUM (RNWT_SUM_OFFICER_POINT_USED.POINT_USED_TOTAL),0)")
    '	sb.AppendLine("    FROM " & Common.Utility.Schema & ".RNWT_SUM_OFFICER_POINT_USED")
    '	sb.AppendLine("    WHERE RNWT_SUM_OFFICER_POINT_USED.OFFICER_ID = RNWM_MAIN_OFFICER_INFO.OFFICER_ID ")
    '	sb.AppendLine("    AND  TO_CHAR(RNWT_SUM_OFFICER_POINT_USED.POINT_USED_DATE,'MM/YYYY') < '" & month & "/" & year & "' ")
    '	sb.AppendLine("    ) ")
    '	sb.AppendLine("),0) ")
    '	sb.AppendLine("as TOTAL_POINT")
    '	sb.AppendLine("FROM " & Common.Utility.Schema & ".RNWM_MAIN_OFFICER_INFO")
    '	sb.AppendLine("LEFT OUTER JOIN " & Common.Utility.Schema & ".RNWT_SUM_OFFICER_POINT")
    '	sb.AppendLine("ON RNWM_MAIN_OFFICER_INFO.OFFICER_ID = RNWT_SUM_OFFICER_POINT.OFFICER_ID")
    '	sb.AppendLine("WHERE RNWM_MAIN_OFFICER_INFO.OFFICER_TYPE = 'S' AND TO_CHAR(RNWT_SUM_OFFICER_POINT.CAL_DATE,'MM/YYYY') = '" & month & "/" & year & "') TBLREPORT WHERE 1=1 ")

    '	If (Not String.IsNullOrEmpty(rvFrom)) And (String.IsNullOrEmpty(rvTo)) Then
    '		sb.AppendLine("AND TOTAL_POINT >= " & rvFrom)
    '	ElseIf (String.IsNullOrEmpty(rvFrom)) And (Not String.IsNullOrEmpty(rvTo)) Then
    '		sb.AppendLine("AND TOTAL_POINT <= " & rvTo)
    '	ElseIf (Not String.IsNullOrEmpty(rvFrom)) And (Not String.IsNullOrEmpty(rvTo)) Then
    '		sb.AppendLine("AND TOTAL_POINT BETWEEN " & rvFrom & " AND " & rvTo & "")
    '	End If
    '	sb.AppendLine("ORDER BY TOTAL_POINT DESC")

    '	Return Excute(sb.ToString)
    'End Function
    Public Shared Function DataUT512Accum(ByVal month As String, ByVal year As String, ByVal rvFrom As String, ByVal rvTo As String) As Data.DataTable
        Dim sb As New Text.StringBuilder

        sb.AppendLine("SELECT * FROM (")
        sb.AppendLine("SELECT  TO_DATE('01/" & month & "/" & year & "','DD/MM/YYYY') AS CAL_DATE,RNWM_MAIN_OFFICER_INFO.OFFICER_APP_ID,")
        sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_FIRSTNAME_THA || ' ' || RNWM_MAIN_OFFICER_INFO.OFFICER_LASTNAME_THA AS OFFICER_FULL_NAME,")
        sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFCIER_MEMBER_DATE,")
        sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFCIER_EXPIRD_DATE,")
        sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFCIER_EXPIRD_DATE + 90 AS OFFCIER_LAST_EXPIRD_DATE,")

        sb.AppendLine("(SELECT SM.STATUS_DESC FROM " & Common.Utility.Schema & ".RNWM_STATUS_MAPPING SM WHERE SM.STATUS_ID = RNWM_MAIN_OFFICER_INFO.OFFICER_STATUS) AS OFFICER_STATUS,")
        sb.AppendLine("NVL(RNWF_SEARCH_POINT_SUMMARY(RNWM_MAIN_OFFICER_INFO.OFFICER_ID, " & year & month & "), 0) ")
        sb.AppendLine("as TOTAL_POINT")
        sb.AppendLine("FROM " & Common.Utility.Schema & ".RNWM_MAIN_OFFICER_INFO")
        sb.AppendLine("LEFT OUTER JOIN " & Common.Utility.Schema & ".RNWT_SUM_OFFICER_POINT")
        sb.AppendLine("ON RNWM_MAIN_OFFICER_INFO.OFFICER_ID = RNWT_SUM_OFFICER_POINT.OFFICER_ID")
        sb.AppendLine("WHERE RNWM_MAIN_OFFICER_INFO.OFFICER_TYPE = 'S' AND TO_CHAR(RNWT_SUM_OFFICER_POINT.CAL_DATE,'MM/YYYY') = '" & month & "/" & year & "') TBLREPORT WHERE 1=1 ")

        If (Not String.IsNullOrEmpty(rvFrom)) And (String.IsNullOrEmpty(rvTo)) Then
            sb.AppendLine("AND TOTAL_POINT >= " & rvFrom)
        ElseIf (String.IsNullOrEmpty(rvFrom)) And (Not String.IsNullOrEmpty(rvTo)) Then
            sb.AppendLine("AND TOTAL_POINT <= " & rvTo)
        ElseIf (Not String.IsNullOrEmpty(rvFrom)) And (Not String.IsNullOrEmpty(rvTo)) Then
            sb.AppendLine("AND TOTAL_POINT BETWEEN " & rvFrom & " AND " & rvTo & "")
        End If
        sb.AppendLine("ORDER BY OFFICER_APP_ID")

        Return Excute(sb.ToString)
    End Function

	Public Shared Function DataUT511(ByVal month As String, ByVal year As String, ByVal rvFrom As String, ByVal rvTo As String) As Data.DataTable
		Dim sb As New Text.StringBuilder

		sb.AppendLine("SELECT * FROM (")
		sb.AppendLine("SELECT  ")
		sb.AppendLine("RNWT_SUM_OFFICER_RV.CAL_DATE,")
		sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_APP_ID,")
		sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_FIRSTNAME_THA || ' ' || RNWM_MAIN_OFFICER_INFO.OFFICER_LASTNAME_THA AS OFFICER_FULLNAME,")
		sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFCIER_MEMBER_DATE,")
		sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFCIER_EXPIRD_DATE,")
		sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFCIER_EXPIRD_DATE + 90 AS EXPIRD_DATE90,")
		sb.AppendLine("(SELECT SM.STATUS_DESC FROM " & Common.Utility.Schema & ".RNWM_STATUS_MAPPING SM WHERE SM.STATUS_ID = RNWM_MAIN_OFFICER_INFO.OFFICER_STATUS) AS OFFICER_STATUS,")
		'sb.AppendLine("DECODE(RNWM_MAIN_OFFICER_INFO.OFFICER_STATUS,'NM','สถานภาพปกติ','EX','อยู่ระหว่างการต่ออายุสาย',")
		'sb.AppendLine("'WT','สถานะอยู่ระหว่างตรวจสอบเอกสารการสมัครแบบออนไลน์','EP','หมดสถานภาพสมาชิก','CE','สถานภาพโมฆะ') AS OFFICER_STATUS,")
		sb.AppendLine("RNWT_SUM_OFFICER_RV.LEFT_RV_SUMMARY,")
		sb.AppendLine("RNWT_SUM_OFFICER_RV.RIGHT_RV_SUMMARY,")
		sb.AppendLine("CASE WHEN RNWT_SUM_OFFICER_RV.LEFT_OLD_RV_SUMMARY < RNWT_SUM_OFFICER_RV.RIGHT_OLD_RV_SUMMARY THEN RNWT_SUM_OFFICER_RV.LEFT_OLD_RV_SUMMARY")
		sb.AppendLine("           ELSE RNWT_SUM_OFFICER_RV.RIGHT_OLD_RV_SUMMARY")
		sb.AppendLine("      END AS TOTAL_OLD_RV_SUMMARY")
		sb.AppendLine(",CASE WHEN RNWT_SUM_OFFICER_RV.LEFT_RV_SUMMARY < RNWT_SUM_OFFICER_RV.RIGHT_RV_SUMMARY THEN RNWT_SUM_OFFICER_RV.LEFT_RV_SUMMARY")
		sb.AppendLine("           ELSE RNWT_SUM_OFFICER_RV.RIGHT_RV_SUMMARY")
		sb.AppendLine("      END AS WEAK_RV")
		'sb.AppendLine("DECODE(RNWT_SUM_OFFICER_RV.LEFT_OLD_RV_SUMMARY,0,RNWT_SUM_OFFICER_RV.RIGHT_OLD_RV_SUMMARY,RNWT_SUM_OFFICER_RV.LEFT_OLD_RV_SUMMARY) as total_OLD_RV_SUMMARY")
		sb.AppendLine("FROM " & Common.Utility.Schema & ".RNWM_MAIN_OFFICER_INFO")
		sb.AppendLine("LEFT OUTER JOIN " & Common.Utility.Schema & ".RNWT_SUM_OFFICER_RV")
		sb.AppendLine("ON RNWM_MAIN_OFFICER_INFO.OFFICER_ID = RNWT_SUM_OFFICER_RV.OFFICER_ID")
		sb.AppendLine("Where RNWM_MAIN_OFFICER_INFO.OFFICER_TYPE = 'S' AND TO_CHAR(RNWT_SUM_OFFICER_RV.CAL_DATE,'MM/YYYY') = '" & month & "/" & year & "'")
		sb.AppendLine(")")
		If (Not String.IsNullOrEmpty(rvFrom)) And (String.IsNullOrEmpty(rvTo)) Then
			sb.AppendLine(" WHERE WEAK_RV  >= " & rvFrom)
		ElseIf (String.IsNullOrEmpty(rvFrom)) And (Not String.IsNullOrEmpty(rvTo)) Then
			sb.AppendLine(" WHERE WEAK_RV <= " & rvTo)
		ElseIf (Not String.IsNullOrEmpty(rvFrom)) And (Not String.IsNullOrEmpty(rvTo)) Then
			sb.AppendLine(" WHERE WEAK_RV BETWEEN " & rvFrom & " AND " & rvTo & "")
		End If
		sb.AppendLine("ORDER BY WEAK_RV DESC")

		Return Excute(sb.ToString)
	End Function

	Public Shared Function DataUT510(ByVal month As String, ByVal year As String, ByVal childFrom As String, ByVal childTo As String) As Data.DataTable
		Dim sb As New Text.StringBuilder
		sb.AppendLine("SELECT * FROM (SELECT  TO_DATE('01/" & month & "/" & year & "','DD/MM/YYYY') AS CAL_DATE,RNWM_MAIN_OFFICER_INFO.OFFICER_APP_ID,")
		sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_FIRSTNAME_THA || ' ' || RNWM_MAIN_OFFICER_INFO.OFFICER_LASTNAME_THA AS OFFICER_FULLNAME,")
		sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFCIER_MEMBER_DATE,")
		sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFCIER_EXPIRD_DATE,")
		sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFCIER_EXPIRD_DATE + 90 AS EXPIRD_DATE90,")
		sb.AppendLine("RNWM_STATUS_MAPPING.STATUS_DESC AS OFFICER_STATUS,")
		sb.AppendLine(" NVL((")
		sb.AppendLine("    SELECT COUNT(SPONSOR_ID)")
		sb.AppendLine("    FROM " & Common.Utility.Schema & ".RNWT_MAIN_OFFICER_DOWNLINE")
		sb.AppendLine("    INNER JOIN " & Common.Utility.Schema & ".RNWM_MAIN_OFFICER_INFO OFFICER_DOWNLINE")
		sb.AppendLine("    ON RNWT_MAIN_OFFICER_DOWNLINE.OFFICER_ID = OFFICER_DOWNLINE.OFFICER_ID")
		sb.AppendLine("    WHERE RNWT_MAIN_OFFICER_DOWNLINE.SPONSOR_ID = RNWM_MAIN_OFFICER_INFO.OFFICER_ID")
		sb.AppendLine("    AND TO_CHAR(OFFICER_DOWNLINE.OFFCIER_MEMBER_DATE,'MM/YYYY') = '" & month & "/" & year & "'")
		sb.AppendLine("    GROUP BY SPONSOR_ID")
		sb.AppendLine("),0) ")
		sb.AppendLine("as TOTAL_CHILD,STATUS_ORDER")
		sb.AppendLine("FROM " & Common.Utility.Schema & ".RNWM_MAIN_OFFICER_INFO")
		sb.AppendLine("LEFT OUTER JOIN " & Common.Utility.Schema & ".RNWT_SUM_OFFICER_INCOME")
		sb.AppendLine("ON RNWM_MAIN_OFFICER_INFO.OFFICER_ID = RNWT_SUM_OFFICER_INCOME.OFFICER_ID")
		sb.AppendLine("LEFT OUTER JOIN " & Common.Utility.Schema & ".RNWM_STATUS_MAPPING ")
		sb.AppendLine("ON RNWT_SUM_OFFICER_INCOME.OFFICER_STATUS = RNWM_STATUS_MAPPING.STATUS_ID")
		sb.AppendLine("WHERE RNWM_MAIN_OFFICER_INFO.OFFICER_TYPE = 'S' AND TO_CHAR(RNWT_SUM_OFFICER_INCOME.CAL_DATE,'MM/YYYY') = '" & month & "/" & year & "' AND RNWT_SUM_OFFICER_INCOME.RECORD_STATUS = 'A'")
		sb.AppendLine(") CHILD")
		If (Not String.IsNullOrEmpty(childFrom)) And (String.IsNullOrEmpty(childTo)) Then
			sb.AppendLine(" WHERE CHILD.TOTAL_CHILD  >= " & childFrom)
		ElseIf (String.IsNullOrEmpty(childFrom)) And (Not String.IsNullOrEmpty(childTo)) Then
			sb.AppendLine(" WHERE CHILD.TOTAL_CHILD <= " & childTo)
		ElseIf (Not String.IsNullOrEmpty(childFrom)) And (Not String.IsNullOrEmpty(childTo)) Then
			sb.AppendLine(" WHERE CHILD.TOTAL_CHILD BETWEEN " & childFrom & " AND " & childTo & "")
		End If
		sb.AppendLine("ORDER BY CHILD.STATUS_ORDER ASC,CHILD.TOTAL_CHILD DESC")

		Return Excute(sb.ToString)
	End Function

	Public Shared Function DataUT509(ByVal month As String, ByVal year As String, ByVal officerStatus As String, ByVal incomeFrom As String, ByVal incomeTo As String) As Data.DataTable
		Dim sb As New Text.StringBuilder
		sb.AppendLine("SELECT")
		sb.AppendLine("RNWT_SUM_OFFICER_INCOME.CAL_DATE,")
		sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_APP_ID,")
		sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_FIRSTNAME_THA || '   ' || RNWM_MAIN_OFFICER_INFO.OFFICER_LASTNAME_THA AS OFFICER_FULLNAME,")
		sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFCIER_MEMBER_DATE,")
		sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFCIER_EXPIRD_DATE,")
		sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFCIER_EXPIRD_DATE + 90 AS EXPIRD_DATE90,")
		sb.AppendLine("(SELECT SM.STATUS_DESC FROM " & Common.Utility.Schema & ".RNWM_STATUS_MAPPING SM WHERE SM.STATUS_ID = RNWM_MAIN_OFFICER_INFO.OFFICER_STATUS) AS OFFICER_STATUS,")
		'sb.AppendLine("DECODE(RNWM_MAIN_OFFICER_INFO.OFFICER_STATUS,'NM','สถานภาพปกติ','EX','อยู่ระหว่างการต่ออายุสาย','WT','สถานะอยู่ระหว่างตรวจสอบเอกสารการสมัครแบบออนไลน์','EP','หมดสถานภาพสมาชิก','CE','สถานภาพโมฆะ') AS OFFICER_STATUS,")
		sb.AppendLine("RNWT_SUM_OFFICER_INCOME.INCOME_TOTAL")
		sb.AppendLine("FROM " & Common.Utility.Schema & ".RNWM_MAIN_OFFICER_INFO INNER JOIN " & Common.Utility.Schema & ".RNWT_SUM_OFFICER_INCOME ON RNWM_MAIN_OFFICER_INFO.OFFICER_ID = RNWT_SUM_OFFICER_INCOME.OFFICER_ID")
		sb.AppendLine("WHERE RNWT_SUM_OFFICER_INCOME.INCOME_TOTAL > 0 AND TO_CHAR(RNWT_SUM_OFFICER_INCOME.CAL_DATE,'MM/YYYY') = '" & month & "/" & year & "'")
		sb.AppendLine("AND RNWT_SUM_OFFICER_INCOME.OFFICER_STATUS = '" & officerStatus & "'")
		If (Not String.IsNullOrEmpty(incomeFrom)) And (String.IsNullOrEmpty(incomeTo)) Then
			sb.AppendLine(" AND RNWT_SUM_OFFICER_INCOME.INCOME_TOTAL >= " & incomeFrom)
		ElseIf (String.IsNullOrEmpty(incomeFrom)) And (Not String.IsNullOrEmpty(incomeTo)) Then
			sb.AppendLine(" AND RNWT_SUM_OFFICER_INCOME.INCOME_TOTAL <= " & incomeTo)
		ElseIf (Not String.IsNullOrEmpty(incomeFrom)) And (Not String.IsNullOrEmpty(incomeTo)) Then
			sb.AppendLine(" AND RNWT_SUM_OFFICER_INCOME.INCOME_TOTAL BETWEEN " & incomeFrom & " AND " & incomeTo & "")
		End If
		sb.AppendLine("ORDER BY RNWT_SUM_OFFICER_INCOME.INCOME_TOTAL DESC")

		Return Excute(sb.ToString)
	End Function

    Public Shared Function DataUT509(ByVal month As String, ByVal year As String, ByVal officerId As String) As Data.DataTable
        Dim sb As New Text.StringBuilder
        sb.AppendLine("SELECT")
        sb.AppendLine("RNWT_SUM_OFFICER_INCOME.CAL_DATE,")
        sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_FIRSTNAME_THA || '   ' || RNWM_MAIN_OFFICER_INFO.OFFICER_LASTNAME_THA AS OFFICER_FULLNAME,")
        sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_APP_ID,")
        sb.AppendLine("RNWT_SUM_OFFICER_INCOME.INCOME_LEVEL_1_SUMMARY, ")
        sb.AppendLine("RNWT_SUM_OFFICER_INCOME.INCOME_LEVEL_2_SUMMARY, ")
        sb.AppendLine("RNWT_SUM_OFFICER_INCOME.INCOME_LEVEL_3_SUMMARY, ")
        sb.AppendLine("RNWT_SUM_OFFICER_INCOME.INCOME_LEVEL_4_SUMMARY, ")
        sb.AppendLine("RNWT_SUM_OFFICER_INCOME.INCOME_LEVEL_5_SUMMARY, ")
        sb.AppendLine("RNWT_SUM_OFFICER_INCOME.INCOME_TOTAL, ")
        sb.AppendLine("RNWT_SUM_OFFICER_INCOME.INCOME_TOTAL_PAID ")
        sb.AppendLine("FROM " & Common.Utility.Schema & ".RNWM_MAIN_OFFICER_INFO INNER JOIN " & Common.Utility.Schema & ".RNWT_SUM_OFFICER_INCOME ON TO_CHAR(RNWM_MAIN_OFFICER_INFO.OFFICER_ID) = TO_CHAR(RNWT_SUM_OFFICER_INCOME.OFFICER_ID)")
        sb.AppendLine("WHERE RNWT_SUM_OFFICER_INCOME.INCOME_TOTAL_PAID > 0 AND TO_CHAR(RNWT_SUM_OFFICER_INCOME.CAL_DATE,'MM/YYYY') = '" & month & "/" & year & "'")

        If Not String.IsNullOrEmpty(officerId) Then
            sb.AppendLine("  AND TO_CHAR(RNWT_SUM_OFFICER_INCOME.OFFICER_ID) = '" & officerId & "'")
        End If

        sb.AppendLine("ORDER BY RNWT_SUM_OFFICER_INCOME.INCOME_TOTAL_PAID DESC")

        Return Excute(sb.ToString)
    End Function

    Public Shared Function DataUT508(ByVal month As String, ByVal year As String, ByVal officerStatus As String, ByVal incomeFrom As String, ByVal incomeTo As String) As Data.DataTable
        Dim sb As New Text.StringBuilder
        sb.AppendLine("SELECT")
        sb.AppendLine("RNWT_SUM_OFFICER_INCOME.CAL_DATE,RNWM_MAIN_OFFICER_INFO.OFFICER_APP_ID,")
        sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_FIRSTNAME_THA || '   ' || RNWM_MAIN_OFFICER_INFO.OFFICER_LASTNAME_THA AS OFFICER_FULLNAME,")
        sb.AppendLine("RNWM_MAIN_BANK_INFO.BANK_NAME_TH AS BANK_NAME,")
        sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_BANK_ACCOUNT_NAME,")
        sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_BANK_ACCOUNT_CODE,")
        sb.AppendLine("RNWT_SUM_OFFICER_INCOME.INCOME_TOTAL,")
        sb.AppendLine("RNWT_SUM_OFFICER_INCOME.INCOME_TOTAL_PAID")
        sb.AppendLine("FROM " & Common.Utility.Schema & ".RNWM_MAIN_OFFICER_INFO LEFT OUTER JOIN " & Common.Utility.Schema & ".RNWT_SUM_OFFICER_INCOME ON RNWM_MAIN_OFFICER_INFO.OFFICER_ID = RNWT_SUM_OFFICER_INCOME.OFFICER_ID")
        sb.AppendLine("LEFT OUTER JOIN RNWM_MAIN_BANK_INFO ON RNWM_MAIN_OFFICER_INFO.BANK_ID = RNWM_MAIN_BANK_INFO.BANK_ID")
        sb.AppendLine("WHERE RNWT_SUM_OFFICER_INCOME.INCOME_TOTAL_PAID > 0 AND TO_CHAR(RNWT_SUM_OFFICER_INCOME.CAL_DATE,'MM/YYYY') = '" & month & "/" & year & "'")
        sb.AppendLine("AND RNWT_SUM_OFFICER_INCOME.OFFICER_STATUS = '" & officerStatus & "'")
        If (Not String.IsNullOrEmpty(incomeFrom)) And (String.IsNullOrEmpty(incomeTo)) Then
            sb.AppendLine(" AND RNWT_SUM_OFFICER_INCOME.INCOME_TOTAL_PAID >= " & incomeFrom)
        ElseIf (String.IsNullOrEmpty(incomeFrom)) And (Not String.IsNullOrEmpty(incomeTo)) Then
            sb.AppendLine(" AND RNWT_SUM_OFFICER_INCOME.INCOME_TOTAL_PAID <= " & incomeTo)
        ElseIf (Not String.IsNullOrEmpty(incomeFrom)) And (Not String.IsNullOrEmpty(incomeTo)) Then
            sb.AppendLine(" AND RNWT_SUM_OFFICER_INCOME.INCOME_TOTAL_PAID BETWEEN " & incomeFrom & " AND " & incomeTo & "")
        End If
        sb.AppendLine("ORDER BY RNWT_SUM_OFFICER_INCOME.INCOME_TOTAL_PAID DESC")
        Return Excute(sb.ToString)
    End Function

    Public Shared Function DataUT507(ByVal incomeFrom As Nullable(Of Decimal), ByVal incomeTo As Nullable(Of Decimal), Optional ByVal oYear As String = "") As Data.DataTable
        Dim sb As New Text.StringBuilder
        sb.AppendLine("SELECT * ")
        sb.AppendLine("FROM ( ")
        sb.AppendLine("SELECT ")
        sb.AppendLine("'' AS T1, ")
        sb.AppendLine("OFFICER_FIRSTNAME_THA AS T2, ")
        sb.AppendLine("OFFICER_LASTNAME_THA AS T3, ")
        sb.AppendLine("OFFICER_FIRSTNAME_ENG || '  ' || OFFICER_FIRSTNAME_ENG AS T4, ")
        sb.AppendLine("TO_CHAR(OFFICER_BIRTHDATE,'YYYYMMDD') AS T5, ")
        sb.AppendLine("OFFICER_SEX AS T6, ")
        sb.AppendLine("OFFICER_MARRIED_STATUS AS T7, ")
        sb.AppendLine("OFFICER_CARD_NO AS T8, ")
        sb.AppendLine("TO_CHAR(OFFICER_CARD_EXPIREDATE,'YYYYMMDD') AS T9, ")
        sb.AppendLine("'' AS T10, ")
        sb.AppendLine("OFFICER_CARD_ADDRESS_NO || ' ' || OFFICER_CARD_ADDRESS_MOO || ' ' || OFFICER_CARD_ADDRESS_BUILDING || ' ' || OFFICER_CARD_ADDRESS_SOI || ' ' || OFFICER_CARD_ADDRESS_STREET || ' ' || OFFICER_CARD_ADDRESS_SUB || ' ' || OFFICER_CARD_ADDRESS_DISTRICT || ' ' || OFFICER_CARD_ADDRESS_PROVINCE AS T11, ")
        sb.AppendLine("'' AS T12, ")
        sb.AppendLine("'' AS T13, ")
        sb.AppendLine("OFFICER_CARD_ADDRESS_POSTCODE AS T14, ")
        sb.AppendLine("OFFICER_MAIL_PHONE AS T15, ")
        sb.AppendLine("OFFICER_MAIL_EMAIL AS T16, ")
        sb.AppendLine("'' AS T17,  '' AS T18, '' AS T19, '' AS T20, '' AS T21, '' AS T22, '' AS T23, '' AS T24, ")
        sb.AppendLine("OFFICER_BANK_ACCOUNT_CODE AS T25, ")
        sb.AppendLine("OFFICER_BANK_ACCOUNT_CODE AS T26, ")
        sb.AppendLine("'' AS T27, '' AS T28, '' AS T29, '' AS T30, '' AS T31, '' AS T32, ")
        sb.AppendLine("'' AS T33, '' AS T34, '' AS T35, '' AS T36, '' AS T37, '' AS T38, ")
        sb.AppendLine("'' AS T39, '' AS T40, '' AS T41, '' AS T42, '' AS T43, ")
        sb.AppendLine("'' AS T44, '' AS T45, '' AS T46, '' AS T47, '' AS T48, ")
        sb.AppendLine("'' AS T49, '' AS T50, '' AS T51, '' AS T52, '' AS T53, ")
        sb.AppendLine("'' AS T54, '' AS T55, '' AS T56, '' AS T57, '' AS T58, '' AS T59, ")
        sb.AppendLine("( ")
        sb.AppendLine("SELECT SUM(RNWT_SUM_OFFICER_INCOME.INCOME_TOTAL_PAID) ")
        sb.AppendLine("FROM RNWT_SUM_OFFICER_INCOME ")
        sb.AppendLine("WHERE RNWT_SUM_OFFICER_INCOME.OFFICER_ID = RNWM_MAIN_OFFICER_INFO.OFFICER_ID ")

        If Not String.IsNullOrEmpty(oYear) AndAlso oYear <> "" Then
            sb.AppendLine("AND TO_CHAR(RNWT_SUM_OFFICER_INCOME.CAL_DATE, 'YYYY') = '" & oYear & "'")
        End If

        sb.AppendLine(") ")
        sb.AppendLine("AS T60, ")
        sb.AppendLine("'' AS T61, '' AS T62, '' AS T63, '' AS T64, '' AS T65, ")
        sb.AppendLine("'' AS T66, '' AS T67, '' AS T68, '' AS T69, '' AS T70, '' AS T71, ")
        sb.AppendLine("'' AS T72, '' AS T73 ")
        sb.AppendLine("FROM RNWM_MAIN_OFFICER_INFO ")
        sb.AppendLine("WHERE OFFICER_TYPE = 'S' ")
        sb.AppendLine("ORDER BY OFFICER_APP_ID ")
        sb.AppendLine(") ")
        sb.AppendLine("WHERE 1=1 ")

        If (Not IsNothing(incomeFrom)) And (IsNothing(incomeTo)) Then
            sb.AppendLine("AND T60 >= " & incomeFrom)
        ElseIf (IsNothing(incomeFrom)) And (Not IsNothing(incomeTo)) Then
            sb.AppendLine("AND T60 <= " & incomeTo)
        ElseIf (Not IsNothing(incomeFrom)) And (Not IsNothing(incomeTo)) Then
            sb.AppendLine("AND T60 BETWEEN " & incomeFrom & " AND " & incomeTo & "")
        End If

        Return Excute(sb.ToString)
    End Function

	Public Shared Function DataUT506(ByVal month As String, ByVal year As String) As Data.DataTable
		Dim sb As New Text.StringBuilder
		sb.AppendLine("SELECT  ")
		sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_APP_ID,")
		sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_FIRSTNAME_THA || '  ' || RNWM_MAIN_OFFICER_INFO.OFFICER_LASTNAME_THA AS OFFICER_FULL_NAME,")
		sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFCIER_MEMBER_DATE,")
		sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFCIER_EXPIRD_DATE,")
		sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFCIER_EXPIRD_DATE + 90 AS EXPIRD_DATE90,")
		sb.AppendLine("(SELECT SM.STATUS_DESC FROM " & Common.Utility.Schema & ".RNWM_STATUS_MAPPING SM WHERE SM.STATUS_ID = RNWM_MAIN_OFFICER_INFO.OFFICER_STATUS) AS OFFICER_STATUS,")
		'sb.AppendLine("DECODE(RNWM_MAIN_OFFICER_INFO.OFFICER_STATUS,'NM','สถานภาพปกติ','EX','อยู่ระหว่างการต่ออายุสาย',")
		'sb.AppendLine("'WT','สถานะอยู่ระหว่างตรวจสอบเอกสารการสมัครแบบออนไลน์','EP','หมดสถานภาพสมาชิก','CE','สถานภาพโมฆะ') AS OFFICER_STATUS,")
		sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_UPLINE,")
		sb.AppendLine("(SELECT OFFICER_UPLINE.OFFICER_FIRSTNAME_THA || '  ' || OFFICER_UPLINE.OFFICER_LASTNAME_THA FROM " & Common.Utility.Schema & ".RNWM_MAIN_OFFICER_INFO OFFICER_UPLINE WHERE OFFICER_UPLINE.OFFICER_ID = RNWM_MAIN_OFFICER_INFO.OFFICER_UPLINE) AS UPLINE_FULLNAME,")
		sb.AppendLine("DECODE(RNWM_MAIN_OFFICER_INFO.OFFICER_UPLINE_SIDE,'L','Left','R','Right') AS OFFICER_UPLINE_SIDE,")
		sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_SPONSOR,")
		sb.AppendLine("(SELECT OFFICER_SPONSOR.OFFICER_FIRSTNAME_THA || '  ' || OFFICER_SPONSOR.OFFICER_LASTNAME_THA FROM " & Common.Utility.Schema & ".RNWM_MAIN_OFFICER_INFO OFFICER_SPONSOR WHERE OFFICER_SPONSOR.OFFICER_ID = RNWM_MAIN_OFFICER_INFO.OFFICER_SPONSOR) AS SPONSOR_FULLNAME")
		sb.AppendLine("FROM " & Common.Utility.Schema & ".RNWM_MAIN_OFFICER_INFO")
		sb.AppendLine("WHERE  TO_CHAR(RNWM_MAIN_OFFICER_INFO.OFFCIER_MEMBER_DATE,'MM/YYYY') = '" & month & "/" & year & "' AND OFFICER_TYPE = 'S'")
		sb.AppendLine("ORDER BY RNWM_MAIN_OFFICER_INFO.OFFCIER_MEMBER_DATE")
		Return Excute(sb.ToString)
	End Function

	Public Shared Function DataUT505(ByVal month As String, ByVal year As String, ByVal officerId As String) As Data.DataTable
		Dim str As String = SearchDownline("3", officerId)
		Return DataLv123(month, year, str)
	End Function

	Public Shared Function DataUT504(ByVal month As String, ByVal year As String, ByVal officerId As String) As Data.DataTable
		Dim str As String = SearchDownline("2", officerId)
		Return DataLv123(month, year, str)
	End Function

	Public Shared Function DataUT503(ByVal month As String, ByVal year As String, ByVal officerId As String) As Data.DataTable
		Dim str As String = SearchDownline("1", officerId)
		Return DataLv123(month, year, str)
	End Function

	Public Shared Function DataUT515(ByVal month As String, ByVal year As String, ByVal officerId As String) As Data.DataTable
		Dim str As String = SearchDownline("4", officerId)
		Return DataLv123(month, year, str)
	End Function

	Public Shared Function DataUT516(ByVal month As String, ByVal year As String, ByVal officerId As String) As Data.DataTable
		Dim str As String = SearchDownline("5", officerId)
		Return DataLv123(month, year, str)
	End Function

	Public Shared Function DataLv123(ByVal month As String, ByVal year As String, ByVal officerId As String) As Data.DataTable
		Dim sb As New Text.StringBuilder
		sb.AppendLine("SELECT RNWT_SUM_OFFICER_RV.CAL_DATE,RNWM_MAIN_OFFICER_INFO.OFFICER_APP_ID,")
		sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_FIRSTNAME_THA || '  ' || RNWM_MAIN_OFFICER_INFO.OFFICER_LASTNAME_THA AS OFFICER_FULLNAME,")
		sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFCIER_MEMBER_DATE,")
		sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFCIER_EXPIRD_DATE,")
		sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFCIER_EXPIRD_DATE + 90 AS EXPIRD_DATE90,")
		sb.AppendLine("(SELECT SM.STATUS_DESC FROM " & Common.Utility.Schema & ".RNWM_STATUS_MAPPING SM WHERE SM.STATUS_ID = RNWM_MAIN_OFFICER_INFO.OFFICER_STATUS) AS OFFICER_STATUS,")
		'sb.AppendLine("DECODE(RNWM_MAIN_OFFICER_INFO.OFFICER_STATUS,'NM','สถานภาพปกติ','EX','อยู่ระหว่างการต่ออายุสาย',")
		'sb.AppendLine("'WT','สถานะอยู่ระหว่างตรวจสอบเอกสารการสมัครแบบออนไลน์','EP','หมดสถานภาพสมาชิก','CE','สถานภาพโมฆะ') AS OFFICER_STATUS ,")
		sb.AppendLine("RNWT_SUM_OFFICER_INCOME.INCOME_MYSELF As MYSELF_RV,")
		sb.AppendLine("RNWT_SUM_OFFICER_RV.LEFT_OLD_RV_SUMMARY + RNWT_SUM_OFFICER_RV.LEFT_RV_SUMMARY AS TOTAL_LEFT_RV_SUMMARY,")
		sb.AppendLine("RNWT_SUM_OFFICER_RV.RIGHT_OLD_RV_SUMMARY + RNWT_SUM_OFFICER_RV.RIGHT_RV_SUMMARY AS TOTAL_RIGHT_RV_SUMMARY,")
		sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_UPLINE,")
		sb.AppendLine("(SELECT OFFICER_UPLINE.OFFICER_FIRSTNAME_THA || '  ' || OFFICER_UPLINE.OFFICER_LASTNAME_THA FROM " & Common.Utility.Schema & ".RNWM_MAIN_OFFICER_INFO OFFICER_UPLINE WHERE OFFICER_UPLINE.OFFICER_ID = RNWM_MAIN_OFFICER_INFO.OFFICER_UPLINE) AS UPLINE_FULLNAME,")
		sb.AppendLine("DECODE(RNWM_MAIN_OFFICER_INFO.OFFICER_UPLINE_SIDE,'L','Left','R','Right') AS OFFICER_UPLINE_SIDE,")
		sb.AppendLine(" RNWM_MAIN_OFFICER_INFO.OFFICER_SPONSOR,")
		sb.AppendLine("OFFICER_SPONSOR.OFFICER_FIRSTNAME_THA || '  ' || OFFICER_SPONSOR.OFFICER_LASTNAME_THA AS SPONSOR_FULLNAME")
		sb.AppendLine("FROM " & Common.Utility.Schema & ".RNWM_MAIN_OFFICER_INFO")
		sb.AppendLine("LEFT OUTER JOIN " & Common.Utility.Schema & ".RNWM_MAIN_OFFICER_INFO OFFICER_SPONSOR")
		sb.AppendLine("ON RNWM_MAIN_OFFICER_INFO.OFFICER_SPONSOR = OFFICER_SPONSOR.OFFICER_ID")
		sb.AppendLine("LEFT OUTER JOIN " & Common.Utility.Schema & ".RNWT_SUM_OFFICER_RV")
		sb.AppendLine("ON RNWM_MAIN_OFFICER_INFO.OFFICER_ID = RNWT_SUM_OFFICER_RV.OFFICER_ID")
		sb.AppendLine("LEFT OUTER JOIN " & Common.Utility.Schema & ".RNWT_SUM_OFFICER_INCOME")
		sb.AppendLine("ON RNWM_MAIN_OFFICER_INFO.OFFICER_ID = RNWT_SUM_OFFICER_INCOME.OFFICER_ID AND  TO_CHAR(RNWT_SUM_OFFICER_INCOME.CAL_DATE,'MM/YYYY') = '" & month & "/" & year & "' AND RNWT_SUM_OFFICER_INCOME.RECORD_STATUS = 'A'")
		sb.AppendLine("WHERE TO_CHAR(RNWT_SUM_OFFICER_RV.CAL_DATE,'MM/YYYY') = '" & month & "/" & year & "' AND RNWT_SUM_OFFICER_RV.RECORD_STATUS = 'A'")
		If String.IsNullOrEmpty(officerId) Then
			sb.AppendLine("  AND 1=2") 'ถ้าไม่เจอ Level ไหนเลยให้แสดงข้อมูลรายงานเป็นไม่มีข้อมูล
		Else
			sb.AppendLine("  AND (" & officerId & ")")
		End If
		sb.AppendLine("ORDER BY RNWM_MAIN_OFFICER_INFO.OFFCIER_MEMBER_DATE ASC")

		Return Excute(sb.ToString)
	End Function

	Public Shared Function DataUT502(ByVal month As String, ByVal year As String, ByVal officerAppId As String) As Data.DataTable
		Dim sb As New Text.StringBuilder
		sb.AppendLine("SELECT * FROM(")
		sb.AppendLine("SELECT")
		sb.AppendLine("RNWT_SUM_OFFICER_INCOME.CAL_DATE,")
		sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_FIRSTNAME_THA || '    ' || RNWM_MAIN_OFFICER_INFO.OFFICER_LASTNAME_THA AS OFFICER_FULL_NAME,")
		sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_APP_ID, ")
		sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFCIER_MEMBER_DATE, ")
		sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFCIER_EXPIRD_DATE,")
		sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFCIER_EXPIRD_DATE + 90 AS EXPIRD_DATE90,")
		sb.AppendLine("RNWT_SUM_OFFICER_RV.MYSELF_RV,")
		sb.AppendLine("RNWT_SUM_OFFICER_INCOME.INCOME_MYSELF,")
		sb.AppendLine("RNWT_SUM_OFFICER_RV.LEFT_OLD_RV_SUMMARY,")
		sb.AppendLine("RNWT_SUM_OFFICER_RV.LEFT_RV_SUMMARY,")
		sb.AppendLine("RNWT_SUM_OFFICER_RV.RIGHT_OLD_RV_SUMMARY,")
		sb.AppendLine("RNWT_SUM_OFFICER_RV.RIGHT_RV_SUMMARY, ")
		sb.AppendLine("RNWT_SUM_OFFICER_INCOME.INCOME_RV,")
		sb.AppendLine("RNWT_SUM_OFFICER_INCOME.INCOME_LEVEL_1_SUMMARY,")
		sb.AppendLine("RNWT_SUM_OFFICER_INCOME.INCOME_LEVEL_2_SUMMARY,")
		sb.AppendLine("RNWT_SUM_OFFICER_INCOME.INCOME_LEVEL_3_SUMMARY,")
		sb.AppendLine("RNWT_SUM_OFFICER_INCOME.INCOME_LEVEL_4_SUMMARY,")
		sb.AppendLine("RNWT_SUM_OFFICER_INCOME.INCOME_LEVEL_5_SUMMARY,")
		sb.AppendLine("RNWT_SUM_OFFICER_POINT.LEFT_POINT_SUMMARY,")
		sb.AppendLine("RNWT_SUM_OFFICER_POINT.RIGHT_POINT_SUMMARY, ")
		sb.AppendLine("(")
		sb.AppendLine("            SELECT SUM (RNWT_SUM_OFFICER_POINT_USED.POINT_USED_TOTAL)")
		sb.AppendLine("            FROM " & Common.Utility.Schema & ".RNWT_SUM_OFFICER_POINT_USED")
		sb.AppendLine("            WHERE RNWT_SUM_OFFICER_POINT_USED.OFFICER_ID = RNWM_MAIN_OFFICER_INFO.OFFICER_ID")
		sb.AppendLine("            GROUP BY OFFICER_ID")
		sb.AppendLine(") ")
		sb.AppendLine("AS OFFCIER_USED_POINT,")
		sb.AppendLine("NVL(RNWT_SUM_OFFICER_INCOME.INCOME_POINT,0) AS INCOME_POINT,")
		sb.AppendLine("RNWT_SUM_OFFICER_INCOME.INCOME_POPULAR,")
		sb.AppendLine("RNWT_SUM_OFFICER_INCOME.INCOME_POPULAY_DOWNLINE,")
        sb.AppendLine("RNWT_SUM_OFFICER_INCOME.INCOME_TOTAL,")
        sb.AppendLine("RNWT_SUM_OFFICER_INCOME.INCOME_TOTAL_PAID")
        sb.AppendLine("FROM " & Common.Utility.Schema & ".RNWM_MAIN_OFFICER_INFO")
		sb.AppendLine("LEFT OUTER JOIN ")
		sb.AppendLine("" & Common.Utility.Schema & ".RNWT_SUM_OFFICER_RV")
		sb.AppendLine("ON RNWM_MAIN_OFFICER_INFO.OFFICER_ID = RNWT_SUM_OFFICER_RV.OFFICER_ID  AND  TO_CHAR(RNWT_SUM_OFFICER_RV.CAL_DATE,'MM/YYYY') = '" & month & "/" & year & "' AND RNWT_SUM_OFFICER_RV.RECORD_STATUS = 'A' ")
		sb.AppendLine("LEFT OUTER JOIN ")
		sb.AppendLine("" & Common.Utility.Schema & ".RNWT_SUM_OFFICER_POINT")
		sb.AppendLine("ON RNWM_MAIN_OFFICER_INFO.OFFICER_ID = RNWT_SUM_OFFICER_POINT.OFFICER_ID AND  TO_CHAR(RNWT_SUM_OFFICER_POINT.CAL_DATE,'MM/YYYY') = '" & month & "/" & year & "' AND RNWT_SUM_OFFICER_POINT.RECORD_STATUS = 'A'")
		sb.AppendLine("LEFT OUTER JOIN ")
		sb.AppendLine("" & Common.Utility.Schema & ".RNWT_SUM_OFFICER_INCOME")
		sb.AppendLine("ON  RNWM_MAIN_OFFICER_INFO.OFFICER_ID = RNWT_SUM_OFFICER_INCOME.OFFICER_ID AND  TO_CHAR(RNWT_SUM_OFFICER_INCOME.CAL_DATE,'MM/YYYY') = '" & month & "/" & year & "' AND RNWT_SUM_OFFICER_INCOME.RECORD_STATUS = 'A'")
		sb.AppendLine("WHERE TO_CHAR(RNWT_SUM_OFFICER_INCOME.CAL_DATE,'MM/YYYY') = '" & month & "/" & year & "' AND RNWM_MAIN_OFFICER_INFO.OFFICER_APP_ID = '" & officerAppId & "'")
		sb.AppendLine(") WHERE ROWNUM = 1")
		Return Excute(sb.ToString)
	End Function

    Public Shared Function DataUT502LRPoint(ByVal month As String, ByVal year As String, ByVal officerId As String) As Data.DataTable
        Dim sb As New Text.StringBuilder
        sb.AppendLine("SELECT NVL(SUM(RNWT_SUM_OFFICER_POINT.LEFT_POINT_SUMMARY),0) ")
        sb.AppendLine("LEFT_POINT_SUMMARY, ")
        sb.AppendLine("NVL(SUM(RNWT_SUM_OFFICER_POINT.RIGHT_POINT_SUMMARY),0) ")
        sb.AppendLine("RIGHT_POINT_SUMMARY ")
        sb.AppendLine("FROM RNWT_SUM_OFFICER_POINT ")
        sb.AppendLine("WHERE RNWT_SUM_OFFICER_POINT.OFFICER_ID =" & officerId)
        sb.AppendLine("AND RNWT_SUM_OFFICER_POINT.RECORD_STATUS = 'A'")
        sb.AppendLine("AND TO_CHAR(RNWT_SUM_OFFICER_POINT.CAL_DATE,'YYYYMM') <= '" & year & month & "'")
        Return Excute(sb.ToString)
    End Function

    Public Shared Function DataUT502PointUsed(ByVal month As String, ByVal year As String, ByVal officerId As String) As Data.DataTable
        Dim sb As New Text.StringBuilder
        sb.AppendLine("SELECT NVL(SUM(RNWT_SUM_OFFICER_POINT_USED.POINT_USED_TOTAL),0) POINT_USED_TOTAL ")
        sb.AppendLine("FROM(RNWT_SUM_OFFICER_POINT_USED) ")
        sb.AppendLine("WHERE(RNWT_SUM_OFFICER_POINT_USED.OFFICER_ID =" & officerId & ")")
        sb.AppendLine("AND RNWT_SUM_OFFICER_POINT_USED.RECORD_STATUS = 'A'")
        sb.AppendLine("AND TO_CHAR(RNWT_SUM_OFFICER_POINT_USED.POINT_USED_DATE,'YYYYMM') <= '" & year & month & "'")
        Return Excute(sb.ToString)
    End Function

	Public Shared Function CountDownlineLv1(ByVal officerId As String) As Integer
		Dim strWhere As String = SearchDownline("1", officerId)
		Using connOraSelect As New DBOracle(Common.Utility.ConnectString, False)
			Return Convert.ToInt32(connOraSelect.ExecuteScalar("SELECT COUNT(*) FROM RNWM_MAIN_OFFICER_INFO WHERE " & strWhere))
		End Using
	End Function

	Public Shared Function CountDownlineLv2(ByVal officerId As String) As Integer
		Dim strWhere As String = SearchDownline("2", officerId)
		Using connOraSelect As New DBOracle(Common.Utility.ConnectString, False)
			Return Convert.ToInt32(connOraSelect.ExecuteScalar("SELECT COUNT(*) FROM RNWM_MAIN_OFFICER_INFO WHERE " & strWhere))
		End Using
	End Function

	Public Shared Function CountDownlineLv3(ByVal officerId As String) As Integer
		Dim strWhere As String = SearchDownline("3", officerId)
		Using connOraSelect As New DBOracle(Common.Utility.ConnectString, False)
			Return Convert.ToInt32(connOraSelect.ExecuteScalar("SELECT COUNT(*) FROM RNWM_MAIN_OFFICER_INFO WHERE " & strWhere))
		End Using
	End Function

	Public Shared Function CountDownlineLv4(ByVal officerId As String) As Integer
		Dim strWhere As String = SearchDownline("4", officerId)
		Using connOraSelect As New DBOracle(Common.Utility.ConnectString, False)
			Return Convert.ToInt32(connOraSelect.ExecuteScalar("SELECT COUNT(*) FROM RNWM_MAIN_OFFICER_INFO WHERE " & strWhere))
		End Using
	End Function

	Public Shared Function CountDownlineLv5(ByVal officerId As String) As Integer
		Dim strWhere As String = SearchDownline("5", officerId)
		Using connOraSelect As New DBOracle(Common.Utility.ConnectString, False)
			Return Convert.ToInt32(connOraSelect.ExecuteScalar("SELECT COUNT(*) FROM RNWM_MAIN_OFFICER_INFO WHERE " & strWhere))
		End Using
	End Function

	Public Shared Function SearchDownline(ByVal lv As String, ByVal officerId As String) As String
		Dim conn As New OracleConnection
		conn.ConnectionString = Common.Utility.ConnectString
		conn.Open()
		Dim cmd As OracleCommand = conn.CreateCommand
		cmd.CommandType = System.Data.CommandType.StoredProcedure

		Select Case lv
			Case "1"
				cmd.CommandText = Common.Utility.Schema & ".RNWP_SEARCH_DOWNLINE_L1"
			Case "2"
				cmd.CommandText = Common.Utility.Schema & ".RNWP_SEARCH_DOWNLINE_L2"
			Case "3"
				cmd.CommandText = Common.Utility.Schema & ".RNWP_SEARCH_DOWNLINE_L3"
			Case "4"
				cmd.CommandText = Common.Utility.Schema & ".RNWP_SEARCH_DOWNLINE_L4"
			Case Else
				cmd.CommandText = Common.Utility.Schema & ".RNWP_SEARCH_DOWNLINE_L5"
		End Select

		Dim param1 As New OracleParameter
		param1.ParameterName = "P_USER_OWMER"
		param1.OracleType = OracleClient.OracleType.Number
		param1.Direction = Data.ParameterDirection.InputOutput
		param1.Value = Convert.ToDecimal(officerId)

		Dim param2 As New OracleParameter
		param2.ParameterName = "P_USER_CAL"
		param1.OracleType = OracleClient.OracleType.Number
		param2.Direction = Data.ParameterDirection.InputOutput
		param2.Value = Convert.ToDecimal(officerId)

		Dim param3 As New OracleParameter
		param3.ParameterName = "VAL_RETURN"
		param3.OracleType = OracleClient.OracleType.LongVarChar
		param3.Direction = Data.ParameterDirection.InputOutput
		param3.Value = " "
		param3.Size = 30000

		cmd.Parameters.Add(param1)
		cmd.Parameters.Add(param2)
		cmd.Parameters.Add(param3)

		cmd.ExecuteNonQuery()

		Dim strVal As String

		Try
			strVal = param3.Value
		Catch ex As System.InvalidCastException
			strVal = String.Empty
		End Try

		'Dim strVal As String = Officer.GetDownline(officerId, lv)
		SearchDownline = String.Empty
		For Each Str As String In strVal.Split("|")
			If (Not String.IsNullOrEmpty(Str)) AndAlso Str.Trim.Length > 0 Then
				SearchDownline &= "RNWM_MAIN_OFFICER_INFO.OFFICER_ID = " & Str & " OR "
			End If
		Next

		If (Not String.IsNullOrEmpty(SearchDownline)) AndAlso SearchDownline.Length > 4 Then
			SearchDownline = SearchDownline.Substring(0, SearchDownline.Length - 4)
		Else
			Return String.Empty
		End If

	End Function

	Public Shared Function DataUT603(ByVal month As String, ByVal year As String, ByVal incomeForm As String, ByVal incomeTo As String, ByVal officerStatus As String) As Data.DataTable
		Dim sb As New Text.StringBuilder
		sb.AppendLine("SELECT ROWNUM AS ORDERNO,ICASH.* FROM (SELECT RNWM_MAIN_OFFICER_INFO.OFFICER_BANK_ACCOUNT_CODE, ")
		sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_BANK_ACCOUNT_NAME, ")
		sb.AppendLine("RNWT_SUM_OFFICER_INCOME.INCOME_TOTAL, ")
        sb.AppendLine("RNWT_SUM_OFFICER_INCOME.INCOME_TOTAL_PAID, ")
        sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_APP_ID, RNWM_MAIN_OFFICER_INFO.OFFICER_ID,")
		sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_MAIL_EMAIL--,TO_CHAR(RNWT_SUM_OFFICER_INCOME.CAL_DATE,'MMYYYY'),RNWM_MAIN_BANK_INFO.BANK_CODE")
		sb.AppendLine("FROM " & Common.Utility.Schema & ".RNWM_MAIN_OFFICER_INFO")
		sb.AppendLine("LEFT OUTER JOIN " & Common.Utility.Schema & ".RNWT_SUM_OFFICER_INCOME")
		sb.AppendLine("ON RNWM_MAIN_OFFICER_INFO.OFFICER_ID = RNWT_SUM_OFFICER_INCOME.OFFICER_ID")
		sb.AppendLine("LEFT OUTER JOIN " & Common.Utility.Schema & ".RNWM_MAIN_BANK_INFO")
		sb.AppendLine("ON RNWM_MAIN_OFFICER_INFO.BANK_ID = RNWM_MAIN_BANK_INFO.BANK_ID")
		sb.AppendLine("WHERE TO_CHAR(RNWT_SUM_OFFICER_INCOME.CAL_DATE,'MMYYYY') = '" & month & year & "'")
        sb.AppendLine("AND RNWM_MAIN_BANK_INFO.BANK_CODE = '002' AND RNWT_SUM_OFFICER_INCOME.INCOME_TOTAL_PAID > 0 AND RNWT_SUM_OFFICER_INCOME.OFFICER_STATUS IN (" & officerStatus & ")")
		If (Not String.IsNullOrEmpty(incomeForm)) And (String.IsNullOrEmpty(incomeTo)) Then
            sb.AppendLine("AND RNWT_SUM_OFFICER_INCOME.INCOME_TOTAL_PAID >= " & incomeForm)
		ElseIf (String.IsNullOrEmpty(incomeForm)) And (Not String.IsNullOrEmpty(incomeTo)) Then
            sb.AppendLine("AND RNWT_SUM_OFFICER_INCOME.INCOME_TOTAL_PAID <= " & incomeTo)
		ElseIf (Not String.IsNullOrEmpty(incomeForm)) And (Not String.IsNullOrEmpty(incomeTo)) Then
            sb.AppendLine("AND RNWT_SUM_OFFICER_INCOME.INCOME_TOTAL_PAID BETWEEN " & incomeForm & " AND " & incomeTo & "")
		End If
        sb.AppendLine("ORDER BY RNWT_SUM_OFFICER_INCOME.INCOME_TOTAL_PAID DESC) ICASH")

		Return Excute(sb.ToString)
	End Function

	Public Shared Function DataUT604(ByVal month As String, ByVal year As String, ByVal incomeForm As String, ByVal incomeTo As String, ByVal officerStatus As String) As Data.DataTable
		Dim sb As New Text.StringBuilder
		sb.AppendLine("SELECT ROWNUM AS ORDERNO,ICASH.* FROM (SELECT '0' || RNWM_MAIN_OFFICER_INFO.OFFICER_BANK_ACCOUNT_CODE AS OFFICER_BANK_ACCOUNT_CODE, ")
		sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_BANK_ACCOUNT_NAME, ")
		sb.AppendLine("RNWM_MAIN_BANK_INFO.BANK_CODE as BANK_NAME, ")
		sb.AppendLine("'0' || SUBSTR(RNWM_MAIN_OFFICER_INFO.OFFICER_BANK_ACCOUNT_CODE,0,3) as BRANCH_CODE,")
        sb.AppendLine("RNWT_SUM_OFFICER_INCOME.INCOME_TOTAL, ")
        sb.AppendLine("RNWT_SUM_OFFICER_INCOME.INCOME_TOTAL_PAID, ")
		sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_APP_ID, ")
		sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_MAIL_EMAIL")
		sb.AppendLine("FROM " & Common.Utility.Schema & ".RNWM_MAIN_OFFICER_INFO")
		sb.AppendLine("LEFT OUTER JOIN " & Common.Utility.Schema & ".RNWT_SUM_OFFICER_INCOME")
		sb.AppendLine("ON RNWM_MAIN_OFFICER_INFO.OFFICER_ID = RNWT_SUM_OFFICER_INCOME.OFFICER_ID")
		sb.AppendLine("LEFT OUTER JOIN " & Common.Utility.Schema & ".RNWM_MAIN_BANK_INFO")
		sb.AppendLine("ON RNWM_MAIN_OFFICER_INFO.BANK_ID = RNWM_MAIN_BANK_INFO.BANK_ID")
		sb.AppendLine("WHERE TO_CHAR(RNWT_SUM_OFFICER_INCOME.CAL_DATE,'MMYYYY') = '" & month & year & "'")
        sb.AppendLine("AND RNWM_MAIN_BANK_INFO.BANK_CODE <> '002' AND RNWT_SUM_OFFICER_INCOME.INCOME_TOTAL_PAID > 0 AND RNWT_SUM_OFFICER_INCOME.OFFICER_STATUS IN (" & officerStatus & ")")
		If (Not String.IsNullOrEmpty(incomeForm)) And (String.IsNullOrEmpty(incomeTo)) Then
            sb.AppendLine("AND RNWT_SUM_OFFICER_INCOME.INCOME_TOTAL_PAID >= " & incomeForm)
		ElseIf (String.IsNullOrEmpty(incomeForm)) And (Not String.IsNullOrEmpty(incomeTo)) Then
            sb.AppendLine("AND RNWT_SUM_OFFICER_INCOME.INCOME_TOTAL_PAID <= " & incomeTo)
		ElseIf (Not String.IsNullOrEmpty(incomeForm)) And (Not String.IsNullOrEmpty(incomeTo)) Then
            sb.AppendLine("AND RNWT_SUM_OFFICER_INCOME.INCOME_TOTAL_PAID BETWEEN " & incomeForm & " AND " & incomeTo & "")
		End If
        sb.AppendLine("ORDER BY RNWT_SUM_OFFICER_INCOME.INCOME_TOTAL_PAID DESC) ICASH")

		Return Excute(sb.ToString)
	End Function

	Public Shared Function DataUT517(ByVal startDte As String, ByVal endDte As String, ByVal importType As String, ByVal importStatus As String, ByVal appTel As String) As Data.DataTable
		Dim sb As New Text.StringBuilder
		sb.AppendLine("SELECT TO_DATE(SUBSTR(FILE_DATE,0,8),'yyyymmdd') AS FILE_DATE, ")
		sb.AppendLine("MOBILE_NO, FILE_TYPE,")
		sb.AppendLine("FILE_STATUS, FILE_FUNCTION,")
		sb.AppendLine("FILE_DESC, FILE_CHANNEL,")
		sb.AppendLine("FILE_CHANNEL_REFERENCE ")
		sb.AppendLine("FROM ")
		sb.AppendLine("(")
		sb.AppendLine("SELECT RNWT_LOG_IVRFTP_FILE.IVR_CREATE_DT AS FILE_DATE, ")
		sb.AppendLine("IVR_MSISDN AS MOBILE_NO, 'IVR FTP File'aS FILE_TYPE,")
		sb.AppendLine("'ERROR' AS FILE_STATUS, IVR_FUNCTION AS FILE_FUNCTION, ")
		sb.AppendLine("IVR_DESC AS FILE_DESC, IVR_CHANNEL AS FILE_CHANNEL, ")
		sb.AppendLine("IVR_CHANNEL_REFERENCE AS FILE_CHANNEL_REFERENCE")
		sb.AppendLine("FROM RNWT_LOG_IVRFTP_FILE ")
		sb.AppendLine("WHERE (UPPER(RNWT_LOG_IVRFTP_FILE.IVR_CHANNEL) <> UPPER('IVR') OR UPPER(RNWT_LOG_IVRFTP_FILE.IVR_CHANNEL_REFERENCE) <> UPPER('Topping complete')) AND RNWT_LOG_IVRFTP_FILE.RECORD_STATUS = 'A'")
		sb.AppendLine("UNION")
		sb.AppendLine("SELECT RNWT_LOG_IVRFTP_FILE.IVR_CREATE_DT AS FILE_DATE, ")
		sb.AppendLine("IVR_MSISDN AS MOBILE_NO, 'IVR FTP File'aS FILE_TYPE,")
		sb.AppendLine("'SUCCESS' AS FILE_STATUS, IVR_FUNCTION AS FILE_FUNCTION, ")
		sb.AppendLine("IVR_DESC AS FILE_DESC, IVR_CHANNEL AS FILE_CHANNEL, ")
		sb.AppendLine("IVR_CHANNEL_REFERENCE AS FILE_CHANNEL_REFERENCE")
		sb.AppendLine("FROM RNWT_LOG_IVRFTP_FILE")
		sb.AppendLine("WHERE UPPER(RNWT_LOG_IVRFTP_FILE.IVR_CHANNEL) = UPPER('IVR') AND UPPER(RNWT_LOG_IVRFTP_FILE.IVR_CHANNEL_REFERENCE) = UPPER('Topping complete') AND RNWT_LOG_IVRFTP_FILE.RECORD_STATUS = 'A'")
		sb.AppendLine("UNION")
		sb.AppendLine("SELECT RNWT_LOG_SBMFTP_FILE.SBM_CREATE_DT AS FILE_DATE, ")
		sb.AppendLine("SBM_MSISDN AS MOBILE_NO, 'SBM FTP File'aS FILE_TYPE, ")
		sb.AppendLine("'ERROR' AS FILE_STATUS, SBM_FUNCTION AS FILE_FUNCTION, ")
		sb.AppendLine("SBM_DESC AS FILE_DESC, SBM_CHANNEL AS FILE_CHANNEL, ")
		sb.AppendLine("SBM_CHANNEL_REFERENCE AS FILE_CHANNEL_REFERENCE")
		sb.AppendLine("FROM RNWT_LOG_SBMFTP_FILE")
		sb.AppendLine("WHERE (UPPER(RNWT_LOG_SBMFTP_FILE.SBM_FUNCTION) <> UPPER('BUY') OR UPPER(RNWT_LOG_SBMFTP_FILE.SBM_STATUS) <> UPPER('SUCCESS')) AND RNWT_LOG_SBMFTP_FILE.RECORD_STATUS = 'A'")
		sb.AppendLine("UNION")
		sb.AppendLine("SELECT RNWT_LOG_SBMFTP_FILE.SBM_CREATE_DT AS FILE_DATE, ")
		sb.AppendLine("SBM_MSISDN AS MOBILE_NO, 'SBM FTP File'aS FILE_TYPE, ")
		sb.AppendLine("'SUCCESS' AS FILE_STATUS, SBM_FUNCTION AS FILE_FUNCTION, ")
		sb.AppendLine("SBM_DESC AS FILE_DESC, SBM_CHANNEL AS FILE_CHANNEL, ")
		sb.AppendLine("SBM_CHANNEL_REFERENCE AS FILE_CHANNEL_REFERENCE")
		sb.AppendLine("FROM RNWT_LOG_SBMFTP_FILE")
		sb.AppendLine("WHERE UPPER(RNWT_LOG_SBMFTP_FILE.SBM_FUNCTION) = UPPER('BUY') AND UPPER(RNWT_LOG_SBMFTP_FILE.SBM_STATUS) = UPPER('SUCCESS') AND RNWT_LOG_SBMFTP_FILE.RECORD_STATUS = 'A' AND RNWT_LOG_SBMFTP_FILE.SBM_PROFILE='TMVMMMLM1'")
		sb.AppendLine("UNION")
		sb.AppendLine("SELECT RNWT_LOG_IVRXML_FILE.TOPUP_DATE AS FILE_DATE, ")
		sb.AppendLine("MOBILE_NO, 'IVR XML File' AS FILE_TYPE, ")
		sb.AppendLine("'ERROR' AS FILE_STATUS, '' AS FILE_FUNCTION, ")
		sb.AppendLine("REF1_IVR AS FILE_DESC, '' AS FILE_CHANNEL, ")
		sb.AppendLine("'' AS FILE_CHANNEL_REFERENCE")
		sb.AppendLine("FROM RNWT_LOG_IVRXML_FILE")
		sb.AppendLine("WHERE UPPER(RNWT_LOG_IVRXML_FILE.RESULT_CODE) <> UPPER('0') AND RNWT_LOG_IVRXML_FILE.RECORD_STATUS = 'A'")
		sb.AppendLine("UNION")
		sb.AppendLine("SELECT RNWT_LOG_IVRXML_FILE.TOPUP_DATE AS FILE_DATE, ")
		sb.AppendLine("MOBILE_NO, 'IVR XML File' AS FILE_TYPE, ")
		sb.AppendLine("'SUCCESS' AS FILE_STATUS, '' AS FILE_FUNCTION, ")
		sb.AppendLine("REF1_IVR AS FILE_DESC, '' AS FILE_CHANNEL, ")
		sb.AppendLine("'' AS FILE_CHANNEL_REFERENCE")
		sb.AppendLine("FROM RNWT_LOG_IVRXML_FILE")
		sb.AppendLine("WHERE UPPER(RNWT_LOG_IVRXML_FILE.RESULT_CODE) = UPPER('0') AND RNWT_LOG_IVRXML_FILE.RECORD_STATUS = 'A'")
		sb.AppendLine(") WHERE 1=1 ")

		If (Not String.IsNullOrEmpty(startDte)) And (String.IsNullOrEmpty(endDte)) Then
			sb.AppendLine("AND SUBSTR(FILE_DATE,1,8) >= " & startDte)
		ElseIf (String.IsNullOrEmpty(startDte)) And (Not String.IsNullOrEmpty(endDte)) Then
			sb.AppendLine("AND SUBSTR(FILE_DATE,1,8) <= " & endDte)
		ElseIf (Not String.IsNullOrEmpty(startDte)) And (Not String.IsNullOrEmpty(endDte)) Then
			sb.AppendLine("AND SUBSTR(FILE_DATE,1,8) BETWEEN " & startDte & " AND " & endDte & "")
		End If

		If Not String.IsNullOrEmpty(importType) Then
			sb.AppendLine(" AND FILE_TYPE='" & importType & "'")
		End If

		If Not String.IsNullOrEmpty(importStatus) Then
			sb.AppendLine(" AND FILE_STATUS='" & importStatus & "'")
		End If

		If Not String.IsNullOrEmpty(appTel) Then
			sb.AppendLine(" AND MOBILE_NO='" & appTel & "'")
		End If

		Return Excute(sb.ToString)
	End Function

    'Create Query By: Ton, From E-mail Date: 29/11/2011, 
    'Update Code By ZaQk, Update Date: 05/11/2011
    Public Shared Function DataUT518New(ByVal memberFrom As String, ByVal memberTo As String, ByVal childDateFrom As String, ByVal childDateTo As String, ByVal childFrom As String, ByVal childTo As String) As Data.DataTable
        Dim sb As New Text.StringBuilder
        sb.AppendLine("SELECT OFFICER_ID, OFFICER_APP_ID, OFFCIER_APP_TEL, ")
        sb.AppendLine("OFFICER_FULL_NAME, OFFCIER_MEMBER_DATE, ")
        sb.AppendLine("OFFCIER_EXPIRD_DATE, OFFCIER_EXPIRD_DATE + 90 AS OFFCIER_LAST_EXPIRD_DATE, ")
        sb.AppendLine("RV_TOTAL, CHILD_NOT_RV, CHILD_TOTAL ")
        sb.AppendLine("FROM ")

        sb.AppendLine("(")
        sb.AppendLine("SELECT RNWM_MAIN_OFFICER_INFO.OFFICER_ID, RNWM_MAIN_OFFICER_INFO.OFFICER_APP_ID, OFFCIER_APP_TEL, ")
        sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_FIRSTNAME_THA || ' ' || RNWM_MAIN_OFFICER_INFO.OFFICER_LASTNAME_THA AS OFFICER_FULL_NAME, ")
        sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFCIER_MEMBER_DATE, ")
        sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFCIER_EXPIRD_DATE, ")
        sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFCIER_EXPIRD_DATE + 90 AS OFFCIER_LAST_EXPIRD_DATE, ")
        sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_STATUS AS OFFICER_STATUS, ")
        sb.AppendLine("RNWF_SEARCH_CHILD_RV_SUMMARY(RNWM_MAIN_OFFICER_INFO.OFFICER_ID, TO_CHAR(TO_DATE('" & memberFrom & "','DD/MM/YYYY'), 'YYYYMMDD'), TO_CHAR(TO_DATE('" & memberTo & "','DD/MM/YYYY'), 'YYYYMMDD')) as RV_TOTAL, ")
        sb.AppendLine("RNWF_SEARCH_CHILD_NOT_RV(RNWM_MAIN_OFFICER_INFO.OFFICER_ID) as CHILD_NOT_RV, ")
        sb.AppendLine("RNWF_COUNT_DOWNLINE(RNWM_MAIN_OFFICER_INFO.OFFICER_ID) as CHILD_TOTAL ")
        sb.AppendLine("FROM RNWM_MAIN_OFFICER_INFO ")
        sb.AppendLine("WHERE  RNWM_MAIN_OFFICER_INFO.OFFICER_TYPE = 'S' ")

        If (Not String.IsNullOrEmpty(childDateFrom) AndAlso childDateFrom <> "") And (String.IsNullOrEmpty(childDateTo) OrElse childDateTo = "") Then
            sb.AppendLine(" AND TO_CHAR(RNWM_MAIN_OFFICER_INFO.OFFCIER_MEMBER_DATE,'YYYYMMDD') >= TO_CHAR(TO_DATE('" & childDateFrom & "','DD/MM/YYYY'), 'YYYYMMDD')")
        ElseIf (String.IsNullOrEmpty(childFrom) OrElse childDateFrom = "") And (Not String.IsNullOrEmpty(childDateTo) AndAlso childDateTo <> "") Then
            sb.AppendLine(" AND TO_CHAR(RNWM_MAIN_OFFICER_INFO.OFFCIER_MEMBER_DATE,'YYYYMMDD') <= TO_CHAR(TO_DATE('" & childDateTo & "','DD/MM/YYYY'), 'YYYYMMDD')")
        ElseIf (Not String.IsNullOrEmpty(childDateFrom) AndAlso childDateFrom <> "") And (Not String.IsNullOrEmpty(childDateTo) AndAlso childDateTo <> "") Then
            sb.AppendLine(" AND TO_CHAR(RNWM_MAIN_OFFICER_INFO.OFFCIER_MEMBER_DATE,'YYYYMMDD') BETWEEN TO_CHAR(TO_DATE('" & childDateFrom & "','DD/MM/YYYY'), 'YYYYMMDD') AND TO_CHAR(TO_DATE('" & childDateTo & "','DD/MM/YYYY'), 'YYYYMMDD')")
        End If

        sb.AppendLine(") ")


        If (Not String.IsNullOrEmpty(childFrom)) And (String.IsNullOrEmpty(childTo)) Then
            sb.AppendLine(" WHERE CHILD_TOTAL >= " & childFrom)
        ElseIf (String.IsNullOrEmpty(childFrom)) And (Not String.IsNullOrEmpty(childTo)) Then
            sb.AppendLine(" WHERE CHILD_TOTAL <= " & childTo)
        ElseIf (Not String.IsNullOrEmpty(childFrom)) And (Not String.IsNullOrEmpty(childTo)) Then
            sb.AppendLine(" WHERE CHILD_TOTAL BETWEEN " & childFrom & " AND " & childTo & "")
        End If

        sb.AppendLine("Order by OFFICER_APP_ID")

        Dim oDT As DataTable = New DataTable
        oDT.TableName = "Data"
        oDT.Columns.Add("OFFICER_ID")
        oDT.Columns.Add("OFFICER_APP_ID")
        oDT.Columns.Add("OFFCIER_APP_TEL")
        oDT.Columns.Add("OFFICER_FULL_NAME")
        oDT.Columns.Add("OFFCIER_MEMBER_DATE", System.Type.GetType("System.DateTime"))
        oDT.Columns.Add("OFFCIER_EXPIRD_DATE", System.Type.GetType("System.DateTime"))
        oDT.Columns.Add("OFFCIER_LAST_EXPIRD_DATE", System.Type.GetType("System.DateTime"))
        oDT.Columns.Add("RV_TOTAL")
        oDT.Columns.Add("RV_300", System.Type.GetType("System.Decimal"))
        oDT.Columns.Add("RV_200", System.Type.GetType("System.Decimal"))
        oDT.Columns.Add("RV_100", System.Type.GetType("System.Decimal"))
        oDT.Columns.Add("RV_0", System.Type.GetType("System.Decimal"))
        oDT.Columns.Add("CHILD_NOT_RV", System.Type.GetType("System.Decimal"))
        oDT.Columns.Add("CHILD_TOTAL", System.Type.GetType("System.Decimal"))

        Dim dtTemp As DataTable = New DataTable
        dtTemp = Excute(sb.ToString)

        If Not IsNothing(oDT) AndAlso dtTemp.Rows.Count > 0 Then
            For Each dr As DataRow In dtTemp.Rows
                Dim oDR As DataRow = oDT.NewRow
                oDR("OFFICER_ID") = dr("OFFICER_ID")
                oDR("OFFICER_APP_ID") = dr("OFFICER_APP_ID")
                oDR("OFFCIER_APP_TEL") = dr("OFFCIER_APP_TEL")
                oDR("OFFICER_FULL_NAME") = dr("OFFICER_FULL_NAME")
                oDR("OFFCIER_MEMBER_DATE") = dr("OFFCIER_MEMBER_DATE")
                oDR("OFFCIER_EXPIRD_DATE") = dr("OFFCIER_EXPIRD_DATE")
                oDR("OFFCIER_LAST_EXPIRD_DATE") = dr("OFFCIER_LAST_EXPIRD_DATE")
                oDR("RV_TOTAL") = dr("RV_TOTAL")
                Dim arrFrom() As String = dr("RV_TOTAL").Split("|")
                oDR("RV_300") = Convert.ToDecimal(arrFrom(0))
                oDR("RV_200") = Convert.ToDecimal(arrFrom(1))
                oDR("RV_100") = Convert.ToDecimal(arrFrom(2))
                oDR("RV_0") = Convert.ToDecimal(arrFrom(3))
                oDR("CHILD_NOT_RV") = Convert.ToDecimal(dr("CHILD_NOT_RV"))
                oDR("CHILD_TOTAL") = Convert.ToDecimal(dr("CHILD_TOTAL"))

                oDT.Rows.Add(oDR)
            Next
            Return oDT
        Else
            Return Nothing
        End If
    End Function

    'Coding By: Ton,Yut, Update Coding Date: 26/10/2011, From E-mail
    'Update Code By ZaQk, Update Query By Ton, Update Date: 05/11/2011
    Public Shared Function DataUT518(ByVal memberFrom As String, ByVal memberTo As String, ByVal childDateFrom As String, ByVal childDateTo As String, ByVal childFrom As String, ByVal childTo As String) As Data.DataTable
        Dim sb As New Text.StringBuilder
        sb.AppendLine("SELECT OFFICER_ID, OFFICER_APP_ID, ")
        sb.AppendLine("OFFICER_FULL_NAME, OFFCIER_MEMBER_DATE, ")
        sb.AppendLine("OFFCIER_EXPIRD_DATE, OFFCIER_LAST_EXPIRD_DATE, ")
        'sb.AppendLine("'' AS OFFICER_STATUS, ")
        sb.AppendLine("nvl(TOTAL_CHILD,0) AS TOTAL_CHILD, nvl(TOTAL_CHILD_MONTH,0) AS TOTAL_CHILD_MONTH, TOTAL_CHILD_NOT_RV, nvl(TOTAL_CHILD,0) - NVL(TOTAL_CHILD_NOT_RV,0) AS TOTAL_CHILD_HAVE_RV ")
        sb.AppendLine("FROM ")
        sb.AppendLine("(")
        sb.AppendLine("SELECT  RNWM_MAIN_OFFICER_INFO.OFFICER_ID, RNWM_MAIN_OFFICER_INFO.OFFICER_APP_ID, ")
        sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_FIRSTNAME_THA || ' ' || RNWM_MAIN_OFFICER_INFO.OFFICER_LASTNAME_THA AS OFFICER_FULL_NAME, ")
        sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFCIER_MEMBER_DATE, ")
        sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFCIER_EXPIRD_DATE, ")
        sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFCIER_EXPIRD_DATE + 90 AS OFFCIER_LAST_EXPIRD_DATE, ")
        sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_STATUS AS OFFICER_STATUS, ")
        sb.AppendLine("(")
        sb.AppendLine("    Select COUNT(SPONSOR_ID)")
        sb.AppendLine("    FROM RNWT_MAIN_OFFICER_DOWNLINE ")
        sb.AppendLine("    INNER JOIN RNWM_MAIN_OFFICER_INFO OFFICER_DOWNLINE")
        sb.AppendLine("    ON RNWT_MAIN_OFFICER_DOWNLINE.OFFICER_ID = OFFICER_DOWNLINE.OFFICER_ID")
        sb.AppendLine("    WHERE RNWT_MAIN_OFFICER_DOWNLINE.SPONSOR_ID = RNWM_MAIN_OFFICER_INFO.OFFICER_ID AND OFFICER_DOWNLINE.OFFICER_TYPE = 'S' AND OFFICER_DOWNLINE.RECORD_STATUS = 'A' ")

        If (Not String.IsNullOrEmpty(childDateFrom) AndAlso childDateFrom <> "") And (String.IsNullOrEmpty(childDateTo) OrElse childDateTo = "") Then
            sb.AppendLine(" AND TO_CHAR(OFFICER_DOWNLINE.OFFCIER_MEMBER_DATE,'YYYYMMDD') >= TO_CHAR(TO_DATE('" & childDateFrom & "','DD/MM/YYYY'), 'YYYYMMDD')")
        ElseIf (String.IsNullOrEmpty(childFrom) OrElse childDateFrom = "") And (Not String.IsNullOrEmpty(childDateTo) AndAlso childDateTo <> "") Then
            sb.AppendLine(" AND TO_CHAR(OFFICER_DOWNLINE.OFFCIER_MEMBER_DATE,'YYYYMMDD') <= TO_CHAR(TO_DATE('" & childDateTo & "','DD/MM/YYYY'), 'YYYYMMDD')")
        ElseIf (Not String.IsNullOrEmpty(childDateFrom) AndAlso childDateFrom <> "") And (Not String.IsNullOrEmpty(childDateTo) AndAlso childDateTo <> "") Then
            sb.AppendLine(" AND TO_CHAR(OFFICER_DOWNLINE.OFFCIER_MEMBER_DATE,'YYYYMMDD') BETWEEN TO_CHAR(TO_DATE('" & childDateFrom & "','DD/MM/YYYY'), 'YYYYMMDD') AND TO_CHAR(TO_DATE('" & childDateTo & "','DD/MM/YYYY'), 'YYYYMMDD')")
        End If

        sb.AppendLine("    GROUP BY SPONSOR_ID")
        sb.AppendLine(") ")
        sb.AppendLine("as TOTAL_CHILD_MONTH, ")
        sb.AppendLine("(")
        sb.AppendLine("    Select COUNT(SPONSOR_ID)")
        sb.AppendLine("    FROM RNWT_MAIN_OFFICER_DOWNLINE")
        sb.AppendLine("    WHERE RNWT_MAIN_OFFICER_DOWNLINE.SPONSOR_ID = RNWM_MAIN_OFFICER_INFO.OFFICER_ID ")
        sb.AppendLine("    GROUP BY SPONSOR_ID ")
        sb.AppendLine(") ")
        sb.AppendLine("as TOTAL_CHILD, ")
        sb.AppendLine("(")
        sb.AppendLine("    RNWF_SEARCH_CHILD_NOT_RV(RNWM_MAIN_OFFICER_INFO.OFFICER_ID)  ")
        sb.AppendLine(" ) ")

        sb.AppendLine("as TOTAL_CHILD_NOT_RV ")
        sb.AppendLine("FROM RNWM_MAIN_OFFICER_INFO WHERE RNWM_MAIN_OFFICER_INFO.OFFICER_TYPE = 'S' AND RNWM_MAIN_OFFICER_INFO.RECORD_STATUS = 'A' ")

        If (Not String.IsNullOrEmpty(memberFrom) AndAlso memberFrom <> "") And (String.IsNullOrEmpty(memberTo) OrElse memberTo = "") Then
            sb.AppendLine(" AND TO_CHAR(RNWM_MAIN_OFFICER_INFO.OFFCIER_MEMBER_DATE,'YYYYMMDD') >= TO_CHAR(TO_DATE('" & memberFrom & "','DD/MM/YYYY'), 'YYYYMMDD')")
        ElseIf (String.IsNullOrEmpty(memberFrom) OrElse memberFrom = "") And (Not String.IsNullOrEmpty(memberTo) AndAlso memberTo <> "") Then
            sb.AppendLine(" AND TO_CHAR(RNWM_MAIN_OFFICER_INFO.OFFCIER_MEMBER_DATE,'YYYYMMDD') <= TO_CHAR(TO_DATE('" & memberTo & "','DD/MM/YYYY'), 'YYYYMMDD')")
        ElseIf (Not String.IsNullOrEmpty(memberFrom) AndAlso memberFrom <> "") And (Not String.IsNullOrEmpty(memberTo) AndAlso memberTo <> "") Then
            sb.AppendLine(" AND TO_CHAR(RNWM_MAIN_OFFICER_INFO.OFFCIER_MEMBER_DATE,'YYYYMMDD') BETWEEN TO_CHAR(TO_DATE('" & memberFrom & "','DD/MM/YYYY'), 'YYYYMMDD') AND TO_CHAR(TO_DATE('" & memberTo & "','DD/MM/YYYY'), 'YYYYMMDD')")
        End If

        sb.AppendLine(") ")

        If (Not String.IsNullOrEmpty(childFrom)) And (String.IsNullOrEmpty(childTo)) Then
            sb.AppendLine(" WHERE TOTAL_CHILD_MONTH >= " & childFrom)
        ElseIf (String.IsNullOrEmpty(childFrom)) And (Not String.IsNullOrEmpty(childTo)) Then
            sb.AppendLine(" WHERE TOTAL_CHILD_MONTH <= " & childTo)
        ElseIf (Not String.IsNullOrEmpty(childFrom)) And (Not String.IsNullOrEmpty(childTo)) Then
            sb.AppendLine(" WHERE TOTAL_CHILD_MONTH BETWEEN " & childFrom & " AND " & childTo & "")
        End If

        sb.AppendLine("Order by OFFICER_APP_ID")

        Return Excute(sb.ToString)
    End Function
    'Public Shared Function DataUT518(ByVal monthFrom As String, ByVal yearFrom As String, ByVal monthTo As String, ByVal yearTo As String, ByVal childFrom As String, ByVal childTo As String) As Data.DataTable
    '    Dim sb As New Text.StringBuilder
    '    sb.AppendLine("SELECT OFFICER_ID, OFFICER_APP_ID, ")
    '    sb.AppendLine("OFFICER_FULL_NAME, OFFCIER_MEMBER_DATE, ")
    '    sb.AppendLine("OFFCIER_EXPIRD_DATE, OFFCIER_LAST_EXPIRD_DATE, ")
    '    sb.AppendLine("nvl(TOTAL_CHILD,0), nvl(TOTAL_CHILD_MONTH,0) AS TOTAL_CHILD_MONTH, TOTAL_CHILD_NOT_RV, nvl(TOTAL_CHILD_MONTH,0) - NVL(TOTAL_CHILD_NOT_RV,0) AS TOTAL_CHILD_HAVE_RV ")
    '    sb.AppendLine("FROM ")
    '    sb.AppendLine("(")
    '    sb.AppendLine("SELECT  RNWM_MAIN_OFFICER_INFO.OFFICER_ID, RNWM_MAIN_OFFICER_INFO.OFFICER_APP_ID, ")
    '    sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_FIRSTNAME_THA || ' ' || RNWM_MAIN_OFFICER_INFO.OFFICER_LASTNAME_THA AS OFFICER_FULL_NAME, ")
    '    sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFCIER_MEMBER_DATE, ")
    '    sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFCIER_EXPIRD_DATE, ")
    '    sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFCIER_EXPIRD_DATE + 90 AS OFFCIER_LAST_EXPIRD_DATE, ")
    '    sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_STATUS AS OFFICER_STATUS, ")
    '    sb.AppendLine("(")

    '    sb.AppendLine("    Select COUNT(SPONSOR_ID)")
    '    sb.AppendLine("    FROM RNWT_MAIN_OFFICER_DOWNLINE ")
    '    sb.AppendLine("    INNER JOIN RNWM_MAIN_OFFICER_INFO OFFICER_DOWNLINE")

    '    sb.AppendLine("    ON RNWT_MAIN_OFFICER_DOWNLINE.OFFICER_ID = OFFICER_DOWNLINE.OFFICER_ID")
    '    sb.AppendLine("    WHERE RNWT_MAIN_OFFICER_DOWNLINE.SPONSOR_ID = RNWM_MAIN_OFFICER_INFO.OFFICER_ID AND OFFICER_DOWNLINE.OFFICER_TYPE = 'S' AND OFFICER_DOWNLINE.RECORD_STATUS = 'A' ")
    '    sb.AppendLine("    AND TO_CHAR(OFFICER_DOWNLINE.OFFCIER_MEMBER_DATE,'YYYYMM') BETWEEN '" & yearFrom & monthFrom & "' AND '" & yearTo & monthTo & "'")
    '    sb.AppendLine("    GROUP BY SPONSOR_ID")
    '    sb.AppendLine(") ")
    '    sb.AppendLine("as TOTAL_CHILD_MONTH, ")
    '    sb.AppendLine("(")

    '    sb.AppendLine("    Select COUNT(SPONSOR_ID)")
    '    sb.AppendLine("    FROM RNWT_MAIN_OFFICER_DOWNLINE")
    '    sb.AppendLine("    WHERE RNWT_MAIN_OFFICER_DOWNLINE.SPONSOR_ID = RNWM_MAIN_OFFICER_INFO.OFFICER_ID ")
    '    sb.AppendLine("    GROUP BY SPONSOR_ID ")
    '    sb.AppendLine(") ")
    '    sb.AppendLine("as TOTAL_CHILD, ")
    '    sb.AppendLine("(")
    '    sb.AppendLine("    RNWF_SEARCH_CHILD_NOT_RV(RNWM_MAIN_OFFICER_INFO.OFFICER_ID)  ")
    '    sb.AppendLine(" ) ")
    '    sb.AppendLine("as TOTAL_CHILD_NOT_RV")
    '    sb.AppendLine("FROM RNWM_MAIN_OFFICER_INFO WHERE RNWM_MAIN_OFFICER_INFO.OFFICER_TYPE = 'S' AND RNWM_MAIN_OFFICER_INFO.RECORD_STATUS = 'A'")
    '    sb.AppendLine(") ")

    '    If (Not String.IsNullOrEmpty(childFrom)) And (String.IsNullOrEmpty(childTo)) Then
    '        sb.AppendLine(" WHERE TOTAL_CHILD_MONTH >= " & childFrom)
    '    ElseIf (String.IsNullOrEmpty(childFrom)) And (Not String.IsNullOrEmpty(childTo)) Then
    '        sb.AppendLine(" WHERE TOTAL_CHILD_MONTH <= " & childTo)
    '    ElseIf (Not String.IsNullOrEmpty(childFrom)) And (Not String.IsNullOrEmpty(childTo)) Then
    '        sb.AppendLine(" WHERE TOTAL_CHILD_MONTH BETWEEN " & childFrom & " AND " & childTo & "")
    '    End If

    '    sb.AppendLine("Order by OFFICER_APP_ID")

    '    Return Excute(sb.ToString)
    'End Function
    'Coding By: Yut, Update Coding Date: 22/10/2011, From E-mail
    'Public Shared Function DataUT518(ByVal monthFrom As String, ByVal yearFrom As String, ByVal monthTo As String, ByVal yearTo As String, ByVal childFrom As String, ByVal childTo As String) As Data.DataTable
    '    Dim sb As New Text.StringBuilder
    '    sb.AppendLine("SELECT OFFICER_ID, OFFICER_APP_ID,  ")
    '    sb.AppendLine("OFFICER_FULL_NAME, OFFCIER_MEMBER_DATE, ")
    '    sb.AppendLine("OFFCIER_EXPIRD_DATE, OFFCIER_LAST_EXPIRD_DATE,")
    '    sb.AppendLine("TOTAL_CHILD,TOTAL_CHILD_MONTH, TOTAL_CHILD_NOT_RV, TOTAL_CHILD - NVL(TOTAL_CHILD_NOT_RV,0) AS TOTAL_CHILD_HAVE_RV")
    '    sb.AppendLine("FROM")
    '    sb.AppendLine("(")
    '    sb.AppendLine("SELECT  RNWM_MAIN_OFFICER_INFO.OFFICER_ID, RNWM_MAIN_OFFICER_INFO.OFFICER_APP_ID,")
    '    sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_FIRSTNAME_THA || ' ' || RNWM_MAIN_OFFICER_INFO.OFFICER_LASTNAME_THA AS OFFICER_FULL_NAME,")
    '    sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFCIER_MEMBER_DATE,")
    '    sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFCIER_EXPIRD_DATE,")
    '    sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFCIER_EXPIRD_DATE + 90 AS OFFCIER_LAST_EXPIRD_DATE,")
    '    sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_STATUS AS OFFICER_STATUS,")
    '    sb.AppendLine("(")
    '    sb.AppendLine("    SELECT COUNT(SPONSOR_ID)")
    '    sb.AppendLine("    FROM RNWT_MAIN_OFFICER_DOWNLINE")
    '    sb.AppendLine("    INNER JOIN RNWM_MAIN_OFFICER_INFO OFFICER_DOWNLINE")
    '    sb.AppendLine("    ON RNWT_MAIN_OFFICER_DOWNLINE.OFFICER_ID = OFFICER_DOWNLINE.OFFICER_ID")
    '    sb.AppendLine("    WHERE RNWT_MAIN_OFFICER_DOWNLINE.SPONSOR_ID = RNWM_MAIN_OFFICER_INFO.OFFICER_ID AND OFFICER_DOWNLINE.OFFICER_TYPE = 'S' AND OFFICER_DOWNLINE.RECORD_STATUS = 'A' ")
    '    sb.AppendLine("    AND TO_CHAR(OFFICER_DOWNLINE.OFFCIER_MEMBER_DATE,'YYYYMM') BETWEEN '" & yearFrom & monthFrom & "' AND '" & yearTo & monthTo & "'")
    '    sb.AppendLine("    GROUP BY SPONSOR_ID")
    '    sb.AppendLine(") ")
    '    sb.AppendLine("as TOTAL_CHILD_MONTH, ")
    '    sb.AppendLine("(")
    '    sb.AppendLine("    SELECT COUNT(SPONSOR_ID)")
    '    sb.AppendLine("    FROM RNWT_MAIN_OFFICER_DOWNLINE")
    '    sb.AppendLine("    WHERE RNWT_MAIN_OFFICER_DOWNLINE.SPONSOR_ID = RNWM_MAIN_OFFICER_INFO.OFFICER_ID ")
    '    sb.AppendLine("    GROUP BY SPONSOR_ID ")
    '    sb.AppendLine(") ")
    '    sb.AppendLine("as TOTAL_CHILD, ")
    '    sb.AppendLine("(")
    '    sb.AppendLine("    RNWF_SEARCH_CHILD_NOT_RV(RNWM_MAIN_OFFICER_INFO.OFFICER_ID)  ")
    '    sb.AppendLine(" ) ")
    '    sb.AppendLine("as TOTAL_CHILD_NOT_RV")
    '    sb.AppendLine("FROM RNWM_MAIN_OFFICER_INFO WHERE RNWM_MAIN_OFFICER_INFO.OFFICER_TYPE = 'S' AND RNWM_MAIN_OFFICER_INFO.RECORD_STATUS = 'A'")
    '    sb.AppendLine(") ")


    '    If (Not String.IsNullOrEmpty(childFrom)) And (String.IsNullOrEmpty(childTo)) Then
    '        sb.AppendLine(" WHERE TOTAL_CHILD_MONTH >= " & childFrom)
    '    ElseIf (String.IsNullOrEmpty(childFrom)) And (Not String.IsNullOrEmpty(childTo)) Then
    '        sb.AppendLine(" WHERE TOTAL_CHILD_MONTH <= " & childTo)
    '    ElseIf (Not String.IsNullOrEmpty(childFrom)) And (Not String.IsNullOrEmpty(childTo)) Then
    '        sb.AppendLine(" WHERE TOTAL_CHILD_MONTH BETWEEN " & childFrom & " AND " & childTo & "")
    '    End If

    '    sb.AppendLine("Order by OFFICER_APP_ID")

    '    Return Excute(sb.ToString)
    'End Function

    'Public Shared Function DataUT518(ByVal month As String, ByVal year As String, ByVal childFrom As String, ByVal childTo As String) As Data.DataTable
    '	Dim sb As New Text.StringBuilder
    '	sb.AppendLine("SELECT OFFICER_ID, OFFICER_APP_ID,  ")
    '	sb.AppendLine("OFFICER_FULL_NAME, OFFCIER_MEMBER_DATE, ")
    '	sb.AppendLine("OFFCIER_EXPIRD_DATE, OFFCIER_LAST_EXPIRD_DATE,")
    '	sb.AppendLine("(SELECT STATUS_DESC FROM RNWM_STATUS_MAPPING WHERE STATUS_ID=OFFICER_STATUS)OFFICER_STATUS, TOTAL_CHILD_MONTH, ")
    '	sb.AppendLine("TOTAL_CHILD, TOTAL_CHILD_HAVE_RV, TOTAL_CHILD - NVL(TOTAL_CHILD_HAVE_RV,0) AS TOTAL_CHILD_NOT_RV")
    '	sb.AppendLine("FROM")
    '	sb.AppendLine("(")
    '	sb.AppendLine("        SELECT  RNWM_MAIN_OFFICER_INFO.OFFICER_ID, RNWM_MAIN_OFFICER_INFO.OFFICER_APP_ID,")
    '	sb.AppendLine("        RNWM_MAIN_OFFICER_INFO.OFFICER_FIRSTNAME_THA || ' ' || RNWM_MAIN_OFFICER_INFO.OFFICER_LASTNAME_THA AS OFFICER_FULL_NAME,")
    '	sb.AppendLine("        RNWM_MAIN_OFFICER_INFO.OFFCIER_MEMBER_DATE,")
    '	sb.AppendLine("        RNWM_MAIN_OFFICER_INFO.OFFCIER_EXPIRD_DATE,")
    '	sb.AppendLine("        RNWM_MAIN_OFFICER_INFO.OFFCIER_EXPIRD_DATE + 90 AS OFFCIER_LAST_EXPIRD_DATE,")
    '	sb.AppendLine("        RNWM_MAIN_OFFICER_INFO.OFFICER_STATUS AS OFFICER_STATUS,")
    '	sb.AppendLine("        (")
    '	sb.AppendLine("            SELECT COUNT(SPONSOR_ID)")
    '	sb.AppendLine("            FROM RNWT_MAIN_OFFICER_DOWNLINE")
    '	sb.AppendLine("            INNER JOIN RNWM_MAIN_OFFICER_INFO OFFICER_DOWNLINE")
    '	sb.AppendLine("            ON RNWT_MAIN_OFFICER_DOWNLINE.OFFICER_ID = OFFICER_DOWNLINE.OFFICER_ID")
    '	sb.AppendLine("            WHERE RNWT_MAIN_OFFICER_DOWNLINE.SPONSOR_ID = RNWM_MAIN_OFFICER_INFO.OFFICER_ID")
    '	sb.AppendLine("            AND TO_CHAR(OFFICER_DOWNLINE.OFFCIER_MEMBER_DATE,'YYYYMM') = '" & year & month & "'")
    '	sb.AppendLine("            GROUP BY SPONSOR_ID")
    '	sb.AppendLine("        ) ")
    '	sb.AppendLine("        as TOTAL_CHILD_MONTH, ")
    '	sb.AppendLine("       (")
    '	sb.AppendLine("            SELECT COUNT(SPONSOR_ID)")
    '	sb.AppendLine("            FROM RNWT_MAIN_OFFICER_DOWNLINE")
    '	sb.AppendLine("            WHERE RNWT_MAIN_OFFICER_DOWNLINE.SPONSOR_ID = RNWM_MAIN_OFFICER_INFO.OFFICER_ID")
    '	sb.AppendLine("            GROUP BY SPONSOR_ID")
    '	sb.AppendLine("        ) ")
    '	sb.AppendLine("        as TOTAL_CHILD, ")
    '	sb.AppendLine("        (")
    '	sb.AppendLine("            RNWF_SEARCH_CHILD_HAVE_RV(RNWM_MAIN_OFFICER_INFO.OFFICER_ID) ")
    '	sb.AppendLine("        ) ")
    '	sb.AppendLine("        as TOTAL_CHILD_HAVE_RV")
    '	sb.AppendLine("        FROM RNWM_MAIN_OFFICER_INFO ")
    '	sb.AppendLine(")")

    '	If (Not String.IsNullOrEmpty(childFrom)) And (String.IsNullOrEmpty(childTo)) Then
    '		sb.AppendLine(" WHERE TOTAL_CHILD_MONTH >= " & childFrom)
    '	ElseIf (String.IsNullOrEmpty(childFrom)) And (Not String.IsNullOrEmpty(childTo)) Then
    '		sb.AppendLine(" WHERE TOTAL_CHILD_MONTH <= " & childTo)
    '	ElseIf (Not String.IsNullOrEmpty(childFrom)) And (Not String.IsNullOrEmpty(childTo)) Then
    '		sb.AppendLine(" WHERE TOTAL_CHILD_MONTH BETWEEN " & childFrom & " AND " & childTo & "")
    '	End If

    '	sb.AppendLine("Order by OFFICER_APP_ID")
    '	Return Excute(sb.ToString)
    'End Function

    Public Shared Function DataUT519(ByVal startDte As String, ByVal endDte As String, ByVal OfficerAppId As String, ByVal appTel As String, ByVal officerName As String) As Data.DataTable

        Dim sb As New Text.StringBuilder

        sb.AppendLine("SELECT UPD_DATE, OFFICER_APP_ID, ")
        sb.AppendLine("OFFICER_BANK_ACCOUNT_CODE, OFFICER_BANK_ACCOUNT_NAME, ")
        sb.AppendLine("STM.STATUS_DESC AS OFFICER_STATUS, OFFCIER_APP_TEL, ")
        sb.AppendLine("OFFICER_CARD_NO, ")
        sb.AppendLine("SPONSER_NAME, ")
        sb.AppendLine("UPLINE_NAME, OFFICER_UPLINE_SIDE, ")
        sb.AppendLine("UPDATE_NAME ")
        sb.AppendLine("FROM ")
        sb.AppendLine("( ")

        'sb.AppendLine("SELECT TO_CHAR(RNWM_MAIN_OFFICER_INFO_HISTORY.UPD_DATE, 'DD/MM/YYYY HH24:MI:SS') UPD_DATE, RNWM_MAIN_OFFICER_INFO_HISTORY.OFFICER_APP_ID, ")
        sb.AppendLine("SELECT RNWM_MAIN_OFFICER_INFO_HISTORY.UPD_DATE UPD_DATE, RNWM_MAIN_OFFICER_INFO_HISTORY.OFFICER_APP_ID, ")
        sb.AppendLine("RNWM_MAIN_OFFICER_INFO_HISTORY.OFFICER_BANK_ACCOUNT_CODE, RNWM_MAIN_OFFICER_INFO_HISTORY.OFFICER_BANK_ACCOUNT_NAME, ")
        sb.AppendLine("RNWM_MAIN_OFFICER_INFO_HISTORY.OFFICER_STATUS, ")
        sb.AppendLine("RNWM_MAIN_OFFICER_INFO_HISTORY.OFFCIER_APP_TEL, RNWM_MAIN_OFFICER_INFO_HISTORY.OFFICER_CARD_NO, ")
        sb.AppendLine("(Select OFFICER_APP_ID FROM RNWM_MAIN_OFFICER_INFO SPONSOR_TABLE ")
        sb.AppendLine("WHERE(SPONSOR_TABLE.OFFICER_ID = RNWM_MAIN_OFFICER_INFO_HISTORY.OFFICER_SPONSOR)) SPONSER_NAME, ")
        sb.AppendLine("(Select OFFICER_APP_ID FROM RNWM_MAIN_OFFICER_INFO UPLINE_TABLE ")
        sb.AppendLine("WHERE(UPLINE_TABLE.OFFICER_ID = RNWM_MAIN_OFFICER_INFO_HISTORY.OFFICER_UPLINE)) UPLINE_NAME, ")
        sb.AppendLine("DECODE(OFFICER_UPLINE_SIDE,'R','ขวา','L','ซ้าย') OFFICER_UPLINE_SIDE, ")
        sb.AppendLine("(SELECT OFFICER_FIRSTNAME_THA || '   ' || OFFICER_LASTNAME_THA FROM RNWM_MAIN_OFFICER_INFO UPLINE_TABLE ")
        sb.AppendLine("WHERE(TO_CHAR(UPLINE_TABLE.OFFICER_ID) = RNWM_MAIN_OFFICER_INFO_HISTORY.UPD_USER_ID)) UPDATE_NAME ")
        sb.AppendLine("FROM(RNWM_MAIN_OFFICER_INFO_HISTORY) ")

        sb.AppendLine("WHERE 1=1 ")

        If Not String.IsNullOrEmpty(startDte) Then
            sb.AppendLine("AND TO_CHAR(RNWM_MAIN_OFFICER_INFO_HISTORY.UPD_DATE, 'YYYYMMDD') >= TO_CHAR(TO_DATE('" & startDte & "','DD/MM/YYYY'), 'YYYYMMDD')")
        End If

        If Not String.IsNullOrEmpty(endDte) Then
            sb.AppendLine("AND TO_CHAR(RNWM_MAIN_OFFICER_INFO_HISTORY.UPD_DATE, 'YYYYMMDD') <= TO_CHAR(TO_DATE('" & endDte & "','DD/MM/YYYY'), 'YYYYMMDD')")
        End If

        sb.AppendLine("AND RNWM_MAIN_OFFICER_INFO_HISTORY.OFFICER_APP_ID LIKE '%" & OfficerAppId & "%' ")
        sb.AppendLine("AND RNWM_MAIN_OFFICER_INFO_HISTORY.OFFCIER_APP_TEL LIKE '%" & appTel & "%' ")

        If Not String.IsNullOrEmpty(officerName) AndAlso officerName <> "" Then
            sb.AppendLine("AND TO_CHAR(RNWM_MAIN_OFFICER_INFO_HISTORY.UPD_USER_ID) IN ")
            sb.AppendLine("(")
            sb.AppendLine("Select TO_CHAR(RNWM_MAIN_OFFICER_INFO.OFFICER_ID) ")
            sb.AppendLine("FROM(RNWM_MAIN_OFFICER_INFO) ")
            sb.AppendLine("WHERE OFFICER_FIRSTNAME_THA || ' ' || OFFICER_LASTNAME_THA  LIKE '%" & officerName & "%' ")
            sb.AppendLine(") ")
        End If

        sb.AppendLine(" UNION ")

        'sb.AppendLine("SELECT  TO_CHAR(RNWM_MAIN_OFFICER_INFO.UPD_DATE, 'DD/MM/YYYY') UPD_DATE, RNWM_MAIN_OFFICER_INFO.OFFICER_APP_ID, ")
        sb.AppendLine("SELECT RNWM_MAIN_OFFICER_INFO.UPD_DATE UPD_DATE, RNWM_MAIN_OFFICER_INFO.OFFICER_APP_ID, ")
        sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_BANK_ACCOUNT_CODE, RNWM_MAIN_OFFICER_INFO.OFFICER_BANK_ACCOUNT_NAME, ")
        sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_STATUS, ")
        sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFCIER_APP_TEL, RNWM_MAIN_OFFICER_INFO.OFFICER_CARD_NO, ")
        sb.AppendLine("(")
        sb.AppendLine("Select OFFICER_APP_ID ")
        sb.AppendLine("FROM RNWM_MAIN_OFFICER_INFO SPONSOR_TABLE ")
        sb.AppendLine("WHERE(SPONSOR_TABLE.OFFICER_ID = RNWM_MAIN_OFFICER_INFO.OFFICER_SPONSOR) ")
        sb.AppendLine(") SPONSER_NAME, ")
        sb.AppendLine("(")
        sb.AppendLine("Select OFFICER_APP_ID ")
        sb.AppendLine("FROM RNWM_MAIN_OFFICER_INFO UPLINE_TABLE ")
        sb.AppendLine("WHERE(UPLINE_TABLE.OFFICER_ID = RNWM_MAIN_OFFICER_INFO.OFFICER_UPLINE) ")
        sb.AppendLine(")  UPLINE_NAME, ")
        sb.AppendLine("DECODE(OFFICER_UPLINE_SIDE,'R','ขวา','L','ซ้าย') OFFICER_UPLINE_SIDE, ")
        sb.AppendLine("(")
        sb.AppendLine("SELECT OFFICER_FIRSTNAME_THA || '   ' || OFFICER_LASTNAME_THA ")
        sb.AppendLine("FROM RNWM_MAIN_OFFICER_INFO UPLINE_TABLE ")
        sb.AppendLine("WHERE (UPLINE_TABLE.OFFICER_ID LIKE RNWM_MAIN_OFFICER_INFO.UPD_USER_ID) ")
        sb.AppendLine(")  UPDATE_NAME ")
        sb.AppendLine("FROM(RNWM_MAIN_OFFICER_INFO) ")

        sb.AppendLine("WHERE 1=1 ")

        If Not String.IsNullOrEmpty(startDte) Then
            sb.AppendLine("AND TO_CHAR(RNWM_MAIN_OFFICER_INFO.UPD_DATE, 'YYYYMMDD') >= TO_CHAR(TO_DATE('" & startDte & "','DD/MM/YYYY'), 'YYYYMMDD')")
        End If

        If Not String.IsNullOrEmpty(endDte) Then
            sb.AppendLine("AND TO_CHAR(RNWM_MAIN_OFFICER_INFO.UPD_DATE, 'YYYYMMDD') <= TO_CHAR(TO_DATE('" & endDte & "','DD/MM/YYYY'), 'YYYYMMDD')")
        End If

        sb.AppendLine("AND RNWM_MAIN_OFFICER_INFO.OFFICER_APP_ID LIKE '%" & OfficerAppId & "%' ")
        sb.AppendLine("AND RNWM_MAIN_OFFICER_INFO.OFFCIER_APP_TEL LIKE '%" & appTel & "%' ")

        If Not String.IsNullOrEmpty(officerName) AndAlso officerName <> "" Then
            sb.AppendLine("AND TO_CHAR(RNWM_MAIN_OFFICER_INFO.UPD_USER_ID) IN ")
            sb.AppendLine("(")
            sb.AppendLine("Select TO_CHAR(RNWM_MAIN_OFFICER_INFO.OFFICER_ID) ")
            sb.AppendLine("FROM(RNWM_MAIN_OFFICER_INFO) ")
            sb.AppendLine("WHERE OFFICER_FIRSTNAME_THA || ' ' || OFFICER_LASTNAME_THA  LIKE '%" & officerName & "%' ")
            sb.AppendLine(") ")
        End If

        sb.AppendLine(") OFC")
        sb.AppendLine("INNER JOIN RNWM_STATUS_MAPPING STM ON OFC.OFFICER_STATUS=STM.STATUS_ID")
        sb.AppendLine("ORDER BY OFFICER_APP_ID, UPD_DATE DESC")

        Return Excute(sb.ToString)
    End Function

    Public Shared Function DataUT520(ByVal startDte As String, ByVal endDte As String, ByVal officerId As String) As Data.DataTable
        Dim sb As New Text.StringBuilder
        sb.AppendLine("SELECT TO_CHAR(RNWT_MAIN_USER_LOGIN.LOGIN_DATE,'DD/MM/YYYY HH24:MI:SS') LOGIN_DATE, ")
        sb.AppendLine("RNWM_MAIN_USER_INFO.USER_ID, ")
        sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_FIRSTNAME_THA || '   ' || ")
        sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_LASTNAME_THA AS OFFICER_FULL_NAME ")
        sb.AppendLine("from RNWT_MAIN_USER_LOGIN ")
        sb.AppendLine("INNER JOIN RNWM_MAIN_USER_INFO ")
        sb.AppendLine("ON RNWT_MAIN_USER_LOGIN.USER_ID = RNWM_MAIN_USER_INFO.USER_ID ")
        sb.AppendLine("LEFT OUTER JOIN RNWM_MAIN_OFFICER_INFO ")
        sb.AppendLine("ON RNWM_MAIN_USER_INFO.OFFCIER_ID = RNWM_MAIN_OFFICER_INFO.OFFICER_ID ")
        sb.AppendLine("WHERE 1=1 ")

        If Not String.IsNullOrEmpty(startDte) Then
            sb.AppendLine("AND TO_CHAR(RNWT_MAIN_USER_LOGIN.LOGIN_DATE, 'YYYYMMDD') >= TO_CHAR(TO_DATE('" & startDte & "','DD/MM/YYYY'), 'YYYYMMDD')")
        End If

        If Not String.IsNullOrEmpty(endDte) Then
            sb.AppendLine("AND TO_CHAR(RNWT_MAIN_USER_LOGIN.LOGIN_DATE, 'YYYYMMDD') <= TO_CHAR(TO_DATE('" & endDte & "','DD/MM/YYYY'), 'YYYYMMDD')")
        End If

        If Not String.IsNullOrEmpty(officerId) Then
            sb.AppendLine("  AND RNWM_MAIN_USER_INFO.OFFCIER_ID = '" & officerId & "'")
        End If

        sb.AppendLine("  ORDER BY RNWT_MAIN_USER_LOGIN.LOGIN_DATE DESC")

        Return Excute(sb.ToString)
    End Function

End Class
